cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yiying Regular Visitor
Regular Visitor

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

Accepted Solutions
vega Member
Member

Re: How to sum the percentage in Power bi

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

7 REPLIES 7
Phil_Seamark Super Contributor
Super Contributor

Re: How to sum the percentage in Power bi

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!

yiying Regular Visitor
Regular Visitor

Re: How to sum the percentage in Power bi

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

Phil_Seamark Super Contributor
Super Contributor

Re: How to sum the percentage in Power bi

Like this?

 

barchart.png


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

Proud to be a Datanaut!

yiying Regular Visitor
Regular Visitor

Re: How to sum the percentage in Power bi

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. 

vega Member
Member

Re: How to sum the percentage in Power bi

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?

yiying Regular Visitor
Regular Visitor

Re: How to sum the percentage in Power bi

Yes.

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

 

vega Member
Member

Re: How to sum the percentage in Power bi

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 47 members 930 guests
Please welcome our newest community members: