Forum Discussion
Grand total calculation
Hi Guys,
I am new to power bi and i need to calculate the grand total in a column. I an having a measure displayed in the table format. measure = SUM(Table1[Sales Amount])/SUM(Table2[Qty]). the Sales amolunt is 920313461.42 the Qty is 3280190.
When i use this measure i am getting the correct values row wise but when you see the Grand total of the colum measure it is wrong.
It divides the sum of the total sales amount with the total sum of the qty and displays it as 280.6. But here i need to get the grand total of the column. How such a small no is displayed i am confused. I need the grand total of the column. PLease help me with it
Hi Anonymous ,
It is probably because the intermediate table is calculating a Blank or Empty value. For instance line 5 in your table.
Adjust the measure to exclude these result or adjust your filters to exclude these situations.
If you want to change the measure, you can add a filter statement to the Ratio New measure.
Since I don't have your data is a bit of guessing, but a blank value is most likely, otherwise adjust the filter accordingly.
Jan
Hi,
Modify your existing measure to
measure = IFERROR(SUM(Table1[Sales Amount])/SUM(Table2[Qty]),BLANK())
Now my measure will work.
10 Replies
Hi Sathish,
The reason for the total to be 280.6 is because PBI does not sum to row values in the Grand Total line, but it recalculates the formula. So the 280.6 is correct. You need a slightly more complex measure to sum your row values.
I used some sample data and I have rows per product category.
I created the following measures:
Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Sales Qty := sum(Sales[Quantity])
Ratio := DIVIDE([Sales Amount], [Sales Qty])
Ratio New :=
Var CategoryRatio =
ADDCOLUMNS ( SUMMARIZE ( 'Sales', 'Product'[Category]), "Ratio", [Ratio] )
var Result = SumX ( CategoryRatio, [Ratio])
return
ResultBasically the intermediate table CategoryRatio calculates the row values, which are than summed to provide the grand total
Just translate it to your own data model / report
Hope this helps
Jan
if this is a solution for you, don't forget to mark it as such. (and kudos are always welcome too), thanks
You can use the above solution. The problem is known as the sum of averages. You can also refer
https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013
I this case instead of doing avg, you have to use sum(a)/sum(b) in the first step.
Hi,
Try this
=IF(HASONEVALUE(Data[rName]),[Measure],SUMX(SUMMARIZE(VALUES(Data[rName]),Data[rName],"ABCD",[Measure]),[ABCD]))
rName if what i see as the title of the first column in the image.
Hope this helps.
Ashish_Mathur Hi,
I had tried your Query but its showing the grand total as Infinity.
Hi,
Modify your existing measure to
measure = IFERROR(SUM(Table1[Sales Amount])/SUM(Table2[Qty]),BLANK())
Now my measure will work.
Hi Anonymous ,
It is probably because the intermediate table is calculating a Blank or Empty value. For instance line 5 in your table.
Adjust the measure to exclude these result or adjust your filters to exclude these situations.
If you want to change the measure, you can add a filter statement to the Ratio New measure.
Since I don't have your data is a bit of guessing, but a blank value is most likely, otherwise adjust the filter accordingly.
Jan
Hi Anonymous ,
Thanks for your Help its working now. Thanks for your time.
