Distinct Sum

[Anonymous]

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.

[dax]

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.

[parry2k · Accepted solution]

@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]

@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]

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