# Create Measure On Distinct Values

• ### Question

• Hi Friends,

I am seeking one help to calculate Measure based on distinct values. Basically I am creating a tabular model in SSAS. Below is sample data

 ID Group Class BalanceSheetAmt CashFlowAmt 1 Hardware Mouse 1000 100 2 Hardware KeyBoard 1000 500 3 Hardware Monitor 1000 400 4 Software Windows 10 1900 700 5 Software Windows 2012 1900 1000 6 Software Office 365 1900 200 7 Misc Mouse Pad 50 50

Now, I want to create 2 Measures i.e. Sum_BalanceSheetAmt & Sum_CashFlowAmt to report data like this

 Group BalanceSheetAmt Mouse KeyBoard Monitor Windows 10 Windows 2012 Office 365 Mouse Pad Hardware 1000 100 500 400 0 0 0 0 Software 1900 0 0 0 700 1000 200 0 Misc 50 0 0 0 0 0 0 50

 2950 100 500 400 700 1000 200 50

I Tried to calculate SUM by getting distinct values but it doesn't seems working

Test1:=Calculate(SUM(Test[BalSheet_Amt]),FILTER('Test',DISTINCT(Test[BalSheet_Amt])))

Looking forward for the suggestions.

Regards

Gurpreet Sethi

• Edited by Monday, January 16, 2017 4:38 AM added
Monday, January 16, 2017 4:36 AM

### All replies

• Hi Gurpreet,

According to your description, you want to create a Matrix report. In this Matrix report ,measure Sum_BalanceSheetAmt will be sliced by group and class.

In this scenario, you can just use SUM function for measures Sum_BalanceSheetAmt & Sum_CashFlowAmt.

See my sample DAX:

Sum_BalanceSheetAmt = sum(Sales[BalanceSheetAmt])

Sum_CashFlowAmt = sum(Sales[CashFlowAmt])

Then create a Matrix report, putting column Group on ROW, Class on COLUMN, measure Sum_BalanceSheetAmt on VALUES.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Tuesday, January 17, 2017 12:49 PM
Monday, January 16, 2017 8:18 AM
• Hi willson,

Thanks for your reply. Further sorry I forget to mentions this. If I create a Matrix report things work fine but if I would like to view this cube in Power Pivot that's where I am facing a challenge.

Regards

Gurpreet Sethi

Monday, January 16, 2017 7:56 PM
• Hi Gurpreet,