Grand total calculation

[Anonymous]

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

[JustJan · Accepted solution]

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 

[JustJan · Accepted solution]

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 

[sathish_kumar1]

Hi @JustJan ,

            Thanks for your Help its working now. Thanks for your time.

[Ashish_Mathur]

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.

[Anonymous]

@Ashish_Mathur  Hi,

           I had tried your Query but its showing the grand total as Infinity.

[Ashish_Mathur · Accepted solution]

Hi,

Modify your existing measure to

measure = IFERROR(SUM(Table1[Sales Amount])/SUM(Table2[Qty]),BLANK())

Now my measure will work.

[sathish_kumar1]

@Ashish_Mathur  Hi,

                          Thanks for you solution its working

[Ashish_Mathur]

You are welcome.

[amitchandak]

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.

[JustJan]

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
Result

Basically 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

[Anonymous]

@JustJan hi,

               I had tried your Query but its showing the grand total as infinity.