Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Issue with sum on row and total values

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]) / (...

Anonymous
4 years ago
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

Anonymous

Not applicable

4 years ago

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

Related content