Issue with sum on row and total values

[Anonymous]

I am using an if statement to check values and it works on row level but on total it uses the total line which give wrong totals

xxx % = if([QTY PY]=0,blank(), (([Average price CY]*[Qty CY]) / ([Average price PY]*[Qty CY]))-1)

Correct on row level but wrong on totals - how can I use a row total ? In Qlikview its easy to change this but how to in Power BI?

[Anonymous · Accepted solution]

Hi @Anonymous ,

In Power BI, the total is not simply the sum of all row values, it will also calculate by the formula.

You may consider to create a new measure by using ISINSCOP() and SUMX() function.

https://docs.microsoft.com/en-us/dax/isinscope-function-dax 

https://docs.microsoft.com/en-us/dax/sumx-function-dax 

measure = IF(ISINSCOPE([axis column]),[xxx %],SUMX(ALLSELECTED('table'),[xxx %]))

It will return xxx % on row level and return the sum of xxx % at total field.

Best Regards,

Jay

[Pragati11]

HI @Anonymous ,

This is a very common issue when creating measures. Chek the following article on how to resolve this:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Thanks,

Pragati