Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sum of Distinct Values

Hello, I am trying to calculate sum of keys for the following table of data

 

ApplicationData SubjectTable Number of Keys
App1AccountA1000
App1CustomerB500
App1AssetsC8000
App1AccountD1000
App1AccountE1000
App2AccountF200
App2CustomerG50
App2AssetsH150
App2AccountI200
App2AccountJ200
App2CustomerK50

 

Filter: None 

Expected Total = (1000+500+8000+200+50+150)=9900

 

Filter: App1 

Expected Total = (1000+500+8000) = 9500

 

Filter: App2, Account

Expected Total = 200

 

Filter: Account

Expected Total = (1000+200) = 1200

 

I have a feeling it can be done with SUMX but not sure how to do it.

 

Need Help
Tutorial Requests
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    Try this measure:

     

    KeyCount =
CALCULATE (
    SUMX (
        SUMMARIZE (
            Keys,
            Keys[Application],
            Keys[Data Subject],
            "NumKeys", MAX ( Keys[Number of Keys] )
        ),
        [NumKeys]
    )
)

    Hope this helps

    David

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

     

    Hi, try with this:

     

    Sum_DistinctValues =
SUMX (
    SUMMARIZE (
        Table1;
        Table1[Application],
        Table1[DataSubject],
        Table1[Number of Keys]
    ),
    [Number of Keys]
)

    Regards

     

    Victor

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Try this measure:

     

    KeyCount =
CALCULATE (
    SUMX (
        SUMMARIZE (
            Keys,
            Keys[Application],
            Keys[Data Subject],
            "NumKeys", MAX ( Keys[Number of Keys] )
        ),
        [NumKeys]
    )
)

    Hope this helps

    David

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    Try this measure

     

    =SUMX(VALUES(Data[Number of Keys]),[Number of Keys])

     

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      this is what I was expecting.

       

      thank you so much.

       

      Regards.,

      Swamy