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
yiying
Helper I
Helper I

How to sum the percentage in Power bi

Here is a example of what I want to do.

Month       Location   Local sales      Total Sales        Percentage

May                NYC            10                  100                     10%

May                LA               10                  100                     10%

June               NYC             20                  100                     20%
June                 LA               20                  100                    20%

July                  LA               30                  100                     30%

Aug                  LA              40                  100                    40%

 

I was doing a barchart using location as  X-axis and using Month as slicer.  If I want to see the percentage for total month, it should be Sum(Local sales)/sum(total sales) instead of just suming up all the percentages. How should I do that? Thank you

 

 

1 ACCEPTED SOLUTION

Okay, you have the answer in your first post. You can create a measure like so:

Answer = 
SUM(Table1[Local Sales])/SUM(Table1[Total Sales])

Then you can create a bar graph with the values as [Answer], Location as the axis, and a slicer with Month as value.
It will produce the following:
Capture.PNG

View solution in original post

7 REPLIES 7
Phil_Seamark
Employee
Employee

Hi @yiying

 

So just to clarify, what would you expect to see if your user selects May in the slicer based on your sample data?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

A Barchart with just two bar:LA and NYC. Both showing 10%. 

Like this?

 

barchart.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes. Selecting a single month is not a problem. When I select all the percentage become crazy because it just add up all the percentage. 

What exactly do you expect to see when all months are selected? The Sum of the local sales divided by the sum of the total sales?
So, in the example you gave us, LA would be 100/400 = 25%.


Am I understanding this correctly?

Yes.

Or group by month. The percentage of July and Aug would be 70/200

 

Okay, you have the answer in your first post. You can create a measure like so:

Answer = 
SUM(Table1[Local Sales])/SUM(Table1[Total Sales])

Then you can create a bar graph with the values as [Answer], Location as the axis, and a slicer with Month as value.
It will produce the following:
Capture.PNG

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.