I have 2 Excel spreadsheets.
The 1st spreadsheet contains the compliance rate for burgers ate, but isn't just a simple compliance / eligibility formula, so that's why it's in a separate sheet.
The 2nd contains over 100k records and is used to show member-level detail. The member detail shows who purchased a burger (eligibile) and who ate it (compliant).
The problem I'm having is that I cannot plot these 2 datasets on my double y axis visual without 1 dataset being grouped together, whether it be the eligibility/compliance metrics summed by all 4 types of burgers for each restaurant or the 4 compliance rates summed to 1 total rate by each restaurant.
I'm trying to see the "percent of burgers ate" on the first y axis and then the "number of burgers that were not eaten" (a value of 0 for compliant) on the other y axis.
First Spreadsheet:
Burger Type | Percent of Burgers Ate | Restaurant | State |
Cheeseburger | 0.9176 | McDonald's | CA |
Hamburger | 0.9274 | McDonald's | CA |
BLT | 0.8578 | McDonald's | CA |
Chicken Sandwich | 0.8594 | McDonald's | CA |
Cheeseburger | 0.8726 | Burger King | MA |
Hamburger | 0.8417 | Burger King | MA |
BLT | 0.8633 | Burger King | MA |
Chicken Sandwich | 0.8573 | Burger King | MA |
Cheeseburger | 0.8760 | Wendy's | MA |
Hamburger | 0.7638 | Wendy's | MA |
BLT | 0.7901 | Wendy's | MA |
Chicken Sandwich | 0.9621 | Wendy's | MA |
Cheeseburger | 0.9201 | Rally's | NY |
Hamburger | 0.9593 | Rally's | NY |
BLT | 0.8338 | Rally's | NY |
Chicken Sandwich | 0.9664 | Rally's | NY |
Second Spreadsheet:
Burger Type | Eligible | Compliant | Restaurant | State |
Cheeseburger | 1 | 1 | McDonald's | CA |
Cheeseburger | 1 | 1 | McDonald's | CA |
Cheeseburger | 1 | 0 | McDonald's | CA |
Hamburger | 1 | 1 | McDonald's | CA |
Hamburger | 1 | 1 | McDonald's | CA |
Hamburger | 1 | 0 | McDonald's | CA |
Hamburger | 1 | 1 | McDonald's | CA |
BLT | 1 | 0 | McDonald's | CA |
BLT | 1 | 1 | McDonald's | CA |
BLT | 1 | 1 | McDonald's | CA |
Chicken Sandwich | 1 | 0 | McDonald's | CA |
Chicken Sandwich | 1 | 1 | McDonald's | CA |
Cheeseburger | 1 | 1 | Burger King | MA |
Cheeseburger | 1 | 0 | Burger King | MA |
Cheeseburger | 1 | 0 | Burger King | MA |
Hamburger | 1 | 0 | Burger King | MA |
BLT | 1 | 1 | Burger King | MA |
BLT | 1 | 1 | Burger King | MA |
Chicken Sandwich | 1 | 0 | Burger King | MA |
Chicken Sandwich | 1 | 1 | Burger King | MA |
Cheeseburger | 1 | 1 | Wendy's | MA |
Cheeseburger | 1 | 1 | Wendy's | MA |
Hamburger | 1 | 0 | Wendy's | MA |
Hamburger | 1 | 1 | Wendy's | MA |
Hamburger | 1 | 1 | Wendy's | MA |
BLT | 1 | 1 | Wendy's | MA |
Chicken Sandwich | 1 | 1 | Wendy's | MA |
Cheeseburger | 1 | 0 | Rally's | NY |
Cheeseburger | 1 | 1 | Rally's | NY |
Hamburger | 1 | 0 | Rally's | NY |
Hamburger | 1 | 1 | Rally's | NY |
BLT | 1 | 1 | Rally's | NY |
BLT | 1 | 0 | Rally's | NY |
Chicken Sandwich | 1 | 1 | Rally's | NY |
Chicken Sandwich | 1 | 0 | Rally's | NY |
Chicken Sandwich | 1 | 1 | Rally's | NY |
I don't want either of the values plotted to be grouped by Percent of Burgers Ate (for all 4 types of burgers) or the number of burgers that were not eaten (for all 4 restaurants). You can see in the image above the 24 represents a sum of non compliant people across all 4 restaurants.
Expected Outcome:
Can you provide the expected outcome based on the sample data you posted? There may not be enough screen real estate.
Sure. I just added it to my post. Basically one axis with those compliance percentages and then a second axis with those who are not compliant without the compliance percentages or non compliance metrics grouping to one number based on the values on the x axis.
Hi @CoreyLearnsBI,
Perhaps you can try to extract the axis field as a unconnected table as use it as axis of the chart, then you can write measure formulas to look up and calculated values based on current axis values from different tables.
Regards,
Xiaoxin Sheng
Measure formulas within Excel or Power BI? And would this eliminate my grouping issue?
Why is gouping a bad thing in this case? What is the story you are planning to tell?
Grouping is bad because I'm dealing with medical providers and trying to show how many non-compliant members there are for each particular "type of burger" instead of it all grouping to 24 on the y axis.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
5 | |
4 | |
2 | |
1 | |
1 |