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
TomLenzmeier
Regular Visitor

Percentage Differences Boggle

This doesn't strike me as being that difficult, but either because it's Monday or my brain isn't working well, I cannot seem to be able to figure out how to calculate a percentage difference!

 

My table has the following columns

 

Store location address, store city, store state, latitude, longitude, customer address, customer city, customer state, longitude, latitude, distance from store, and distance category. In regards to distance category, there are four: 0-3 Miles, 3-5 Miles, 5-10 Miles, and 10+ Miles. I have a measure that does a simple count and when I toss it into a column chart, I get the count for each category. What am I missing? It should be straightforward: category count divided by total count.

1 ACCEPTED SOLUTION

I had already been down that road. The solution was to add the order to the tool tips (minimum), then select it from the "More Options" on the visual itself.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@TomLenzmeier it is not very clear what you are trying to achieve and what is not working? Can you share sample data and expected results.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

One measure with the axis being the distance category.One measure with the axis being the distance category.

All I want to do is calculate the percentages of each column. I could create a measure for each category and then a percentage measure too, but that seems a bit tedious.

@TomLenzmeier add following measure to get %

 

% = 
DIVIDE(
COUNT(Table[Distance Category]),
 CALCULATE( COUNT( Table[Distance Category] ), ALLSELECTED( Table[Distance Category] ) )

you can also have a measure for count and replace COUNT in above expression with your measure



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I tried that, but it returns 1 for all categories:

 

Pct = DIVIDE([Distance Count], CALCULATE([Distance Count], ALLSELECTED('Distance Category'[Distance Category])))
 
Distance Count = COUNT(Mbr_Geo_Data[Mbshp_Address])

@TomLenzmeierhmm, seems like I'm missing something here, looks like you have multiple table with relationship, can you share what does that relationship looks like

 

also can you make sure on x-axis you are using the same distance category column which you used in DAX ALLSELECTED Function.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

For a moment, it worked, but now I'm befuddled.All I am using this other table for is to get the X axis member to appear in order.All I am using this other table for is to get the X axis member to appear in order.

 

The Pct formula:

Pct = DIVIDE([Distance Count], CALCULATE([Distance Count], ALLSELECTED('Distance Category'[Category])))
Percentages are correct, but the order is off.Percentages are correct, but the order is off.
If I replace "Category" with "Distance Category", then I get all values equal to 1.0
 

HI, @TomLenzmeier 

You could use Sort by column in Power BI Desktop for order.

for the result is 1.0, it involves row context in calculation, you could learn about it.

If you still have problem, please share your sample pbix file.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I had already been down that road. The solution was to add the order to the tool tips (minimum), then select it from the "More Options" on the visual itself.

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.