Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdrammeh
Helper III
Helper III

How to calculate the Percent of Grand Total in Power Query

Hello Friends,

 

I understand this is not a Power Query forum but I want to understand first how to calculate the percentage of a grand total in my data set using both Power Query and Power BI.

 

I have provided an example below. 

 

DescriptionNo of UnitsUnits SoldPercentage of units Sold 
James28625288%Created a custom calculated column and the values are coming correctly
Wendy25317770%
Charlie905157%
Kim11436%
Ben2150%
Sara100%
Grand Total64348575% (485/643)This is not being calculated correctly in Power BI, it  shows either 100% or sum of the values.
1 ACCEPTED SOLUTION
danextian
Super User
Super User

I'm not sure what you mean by creating the calculation in Power Query but this would be simple in DAX

 

Pct Sold =
DIVIDE ( SUM ( 'Table'[Units Sold] ), SUM ( 'Table'[No of Units] ) )

Or if you really want it in PQ, you can add a custom column with this formula

 

=[Units Sold]/[No of Units]

Or if you want to sum and divide the two columns

List.Sum(#"Changed Type"[Units Sold])/List.Sum(#"Changed Type"[No of Units])

What this will do is it will divide the sum of Units Sold column by sum of No of Units Column. In this case, the added column will have the same value for each row.

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

I'm not sure what you mean by creating the calculation in Power Query but this would be simple in DAX

 

Pct Sold =
DIVIDE ( SUM ( 'Table'[Units Sold] ), SUM ( 'Table'[No of Units] ) )

Or if you really want it in PQ, you can add a custom column with this formula

 

=[Units Sold]/[No of Units]

Or if you want to sum and divide the two columns

List.Sum(#"Changed Type"[Units Sold])/List.Sum(#"Changed Type"[No of Units])

What this will do is it will divide the sum of Units Sold column by sum of No of Units Column. In this case, the added column will have the same value for each row.

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
lovermann
Frequent Visitor

I guess summarization of percentages doesn't make sense.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.