How to get the total for dynamic measures

[Snehasishsar]

Hello Friends,

I have a table (Table 1) where the "Threshold “values are coming from a parameter(which user can change) and for employees where the salary(Data Column) is less than "Threshold", we have to calculate the additional cost for that employee and then the total of these additional costs.

However, if you see table 2(powerbi output), the additional cost at the employee level is getting calculated correctly, but however for the total of these additional costs , the aggregation is not happening at a column level.

Instead it is considering the column totals of salary and "Threshold" to compute the Total of these additional Cost. Refer to the screenshot below

The desired result for "Total of these Addtional cost"  is 9500 and not 4500. 

Any help to achieve the result as shown in the desired table would be highly appreciated. 🙂 🙂 

[v-gizhi-msft · Accepted solution]

Hi,

Please try this measure:

Additional Cost = SUMX(DISTINCT('Table'[EmpID]),CALCULATE(IF(MAX('Table'[Salary])>=[Threshold Value],0,[Threshold Value]-MAX('Table'[Salary]))))

When you select one value in Threshold slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

Try a new measure like

additional Cost 1=

sumx(summarize(table, table[emp id],"_sum",[additional Cost]),[_sum])