Wrong grand total by dax measure in power bi desktop

0
votes

I have 3 columns sales, profit, quantity and I am calculating two measure sales_per_unit and profit_per_unit: 

Profit_Per_Unit = DIVIDE(SUM(Orders[Profit]), SUM(Orders[Quantity]))

Sales_Per_Unit = DIVIDE(SUM(Orders[Sales]), SUM(Orders[Quantity]))

I am getting correct answer for all rows but grand total is wrong as you can see in below screenshot:

enter image description here

So I want to understand two things:

  1. why this is happening
  2. what is the solution

EDIT

by using Sales_Per_Unit2 = SUMX(Orders, DIVIDE(Orders[Sales],Orders[Quantity])) it gives me grand total as expected but getting wrong answer for some of the rows as highlighted in below image

enter image description here

UPDATE

i have searched internet and people are using HASONEVALUE function in if condition to filterout different formula for true context and different formula for false context so i also used that formula combined with @MarcoVos answer and i got blank result for the rows where i am getting wrong result but i still not understood full scenario that what is wrong am i doing but i got some clue that formula is right but there is some thing wrong with row context so i thought to update in my question.

If_Sales = IF(HASONEVALUE(Orders[Sales]),SUMX(Orders, DIVIDE(Orders[Sales], Orders[Quantity])))

enter image description here

1
powerbidax
What exactly is "wrong" with the grand total? It looks perfectly fine to me. - RADO
Where you get the "wrong" values, the values for Quantity and Sales are probably aggregates. So for those values there are more than 1 rows with the same [Order ID] and [Orderdate]. To test this, you can use Sales_Per_Unit3 = IF ( COUNTROWS('Orders') > 1, ">1 row" , SUMX( 'Orders', DIVIDE( Orders[Sales], Orders[Quantity] ) ) ) - Marco Vos
If you can see in second row where quantity is 9 and sales is 699.19 so if i divide sales / quantity then is should give me 77.69 which i am able to get using sales_per_unit formula but problem with that measure is that it gives me an wrong grand total and if i use sales_per_unit2 measure which is giving me right grand total but it gives me 200.28 for 699.19/9 which is wrong so how can i achieve right answer for row level and also in grand total - Divyang patel

1 Answers

0
votes

For the totals the DAX-expresions you are using now calculate a division of the sums of each column.

When you want a sum of the results of the divisions at row level, you need to use a function that iterates. SUMX in this case. Something like this:

Sales_Per_Unit = SUMX('Orders', DIVIDE( Orders[Sales], Orders[Quantity] ) )

For the other rows the results will be the same, because the filtercontext there will filter the Orders table to one row.