locked
Sum of distinct values RRS feed

  • Question

  • I am struggling with the issue how to get the right measure (COUNT SPREADS) to sum distinct "sprd_nr" by "key_1".

    Here is a data set example (lets call it "Table1" )

    key_1     sprd_nr     ....
    2017 1_CZECH REPUBLIC 2
    2017 1_CZECH REPUBLIC 2
    2017 1_CZECH REPUBLIC 4
    2017 1_CZECH REPUBLIC 4
    2017 1_CZECH REPUBLIC 4
    2017 2_CZECH REPUBLIC 2
    2017 2_CZECH REPUBLIC 2
    2017 2_CZECH REPUBLIC 2
    2017 2_CZECH REPUBLIC 6
    2017 2_CZECH REPUBLIC 8
    2017 3_CZECH REPUBLIC 2
    2017 3_CZECH REPUBLIC 2
    2017 3_CZECH REPUBLIC 2
    2017 3_CZECH REPUBLIC 2
    2017 3_CZECH REPUBLIC 10
    2017 3_CZECH REPUBLIC 10
    2017 3_CZECH REPUBLIC 16
    2017 3_CZECH REPUBLIC 16
    2017 3_CZECH REPUBLIC 16
    ...

    Desired output:



      SPREAD COUNT
    2017 1_CZECH REPUBLIC 2
    2017 2_CZECH REPUBLIC 3
    2017 3_CZECH REPUBLIC 3
    TOTAL_key_1 8

    And here is the SPREAD COUNT measure I have so far but it is not working on total (TOTAL_key_1)

    SPREAD COUNT :=
    SUMX ( DISTINCT ( 'Table1'[key_1] ), DISTINCTCOUNT ( 'Table1'[sprd_nr] ) )

    Any ideas very appreciate!




    Friday, October 13, 2017 2:25 PM

Answers

  • Hi Bartek,

    Thanks for your quick response.

    In this scenario, you can just replace GROUPBY with SUMMARIZE, see below image:


    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

    Monday, October 16, 2017 8:23 AM

All replies

  • Sunday, October 15, 2017 2:35 AM
  • Hi Bartek,

    Thanks for your question.

    In this scenario, you can create a measure with below formula:

    SPREAD COUNT =
    COUNTROWS ( GROUPBY ( Table1, Table1[Key_1], Table1[sprd_nr] ) )


    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

    Monday, October 16, 2017 1:41 AM
  • Thanks willson yuan

    In my version of DAX (power pivot for Excel 2013) GROUPBY is not available.

    Is there any workaround?

    B

    Monday, October 16, 2017 8:16 AM
  • Hi Bartek,

    Thanks for your quick response.

    In this scenario, you can just replace GROUPBY with SUMMARIZE, see below image:


    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

    Monday, October 16, 2017 8:23 AM
  • Thanks Herbert

    Your proposal assume to modify the source table, which is not my purpose (Or to create a support table only for this calculation - last resort). I prefer a measure

    Monday, October 16, 2017 8:30 AM
  • Thanks willson yuan

    It's working!

    Monday, October 16, 2017 9:04 AM