cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Capture.PNG

2 ACCEPTED SOLUTIONS
JustJan
Responsive Resident
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.

 

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.

 

2020-01-19 11_09_43-Window.jpg

 

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 

View solution in original post

Hi,

Modify your existing measure to

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

Now my measure will work.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
JustJan
Responsive Resident
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.

 

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.

 

2020-01-19 11_09_43-Window.jpg

 

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 

View solution in original post

Hi @JustJan ,

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

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur  Hi,

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

Hi,

Modify your existing measure to

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

Now my measure will work.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

@Ashish_Mathur  Hi,

                          Thanks for you solution its working

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!
JustJan
Responsive Resident
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])
return
Result

Basically the intermediate table CategoryRatio calculates the row values, which are than summed to provide the grand total

 

 

2020-01-18 08_27_19-Window.jpg

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.Capture1.PNG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.