Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct Sum

Hi all , 

 

I have three columns ,

 

Month    Amount   Rate
Jan           95000        35
Jan             6552        35
Jan             1556       35
Jan           95000       35
Jan             6552       35
Feb          65000       45
Feb            7888      45
Feb            1992       45
Feb            65000    45


I need to obtain the sum of distinct values in the Amount column and divide it by the rate. The values of the rate column change with respect to months , but they all would be the same for that month.

 

Required output needs to be like this , For Jan : (95000 + 6552 + 1556)/35

For Feb : (65000  + 7888 + 1992)/45.

 

Can this be done in DAX , i need all values for all the months under a single column , so when i use slicers it gets filtered.

 

Please point me towards how i can achieve this , thanks , sorry for the data in a non - tabled way , i keep getting a HTML error when i try posting tables.

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try this

     

    Measure = 
DIVIDE ( 
SUMX ( VALUES ( Table[Rate] ), CALCULATE ( MAX ( Table[Amount] ) ) ),
MAX ( Table[Rate] )
)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try this

     

    Measure = 
DIVIDE ( 
SUMX ( VALUES ( Table[Rate] ), CALCULATE ( MAX ( Table[Amount] ) ) ),
MAX ( Table[Rate] )
)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k  , thank you.

       

      The measure works when i have a month selected.

      Is there a way to modify the formula to get the sum of all months when no month is selected?

       

      Thanks.

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous it should work in both the scenario, what is not working when no month is selected?

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You could refer to my sample for details.

    Measure 4 = SUMX(SUMMARIZE(T1,T1[Rate],T1[Month],T1[Amount],"distict", AVERAGE(T1[Amount])),[distict])/MIN(T1[Rate])

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.