Can I create a single visualization with a double y axis without it grouping my dataset?

[CoreyLearnsBI]

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:

[lbendlin]

Can you provide the expected outcome based on the sample data you posted? There may not be enough screen real estate.

[CoreyLearnsBI]

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.

[v-shex-msft]

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

[CoreyLearnsBI]

Measure formulas within Excel or Power BI? And would this eliminate my grouping issue?

[lbendlin]

Why is gouping a bad thing in this case?  What is the story you are planning to tell?

[CoreyLearnsBI]

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.