locked
Create Measure On Distinct Values RRS feed

  • 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


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


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

All replies

  • Hi Gurpreet, 

    Thanks for your question. 

    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 alexander fun 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


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

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

    Thanks for your response.

    It is not necessary to create such complex measures.  In Power Pivot, you can also create Matrix report, putting column Group on ROW, Class on COLUMN, measure Sum_BalanceSheetAmt on VALUES.

    See my sample Power Pivot report:


    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 alexander fun Tuesday, January 17, 2017 12:49 PM
    Tuesday, January 17, 2017 1:25 AM