cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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

2 ACCEPTED SOLUTIONS
Responsive Resident

Hi @Anonymous ,

It is probably because the intermediate table is calculating a Blank or Empty value. For instance line 5 in your table.

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

Super User

Hi,

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

Now my measure will work.

Regards,
Ashish Mathur
http://www.ashishmathur.com
10 REPLIES 10
Responsive Resident

Hi @Anonymous ,

It is probably because the intermediate table is calculating a Blank or Empty value. For instance line 5 in your table.

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

Frequent Visitor

Hi @JustJan ,

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

Super User

Hi,

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

Now my measure will work.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks for you solution its working

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Responsive Resident

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])returnResult`

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
Not applicable

@JustJan hi,

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!