How to build a visual with these requirements?

[Stuart95051]

Let's say I've data like this:

	Sweet	Sour/Tart	Umami	Spicy	Bitter	Vegan
Sweet n Sour Pork	1	1	1
Mapo Tofu	1		1	1
Sushi			1
Soy Burger			1			1
Lagsane	1	1	1
Apple Pie	1

I want to build a visual to show % of my menu which has the Sweet characteristics, % of my menu which has the Sour characteristics, % of my menu which has the Umami characteristics, and so on an so forth.  It's easy to do this in Excel, I'd first need to count the number of items in my menu.  How do I do the same in PowerBi so that it can be used to calculate the % later?  

Thanks!

[Anonymous · Accepted solution]

Hi @Stuart95051 ,

I create a table as you mentioned and I think you can let Bitter column become 0.

Then I think you can create six measures and here is a common template for these measures, you can change their respective names as needed.

SweetPercent =
DIVIDE (
    CALCULATE ( COUNTROWS ( 'Table' ), VALUE ( 'Table'[Sweet] ) = 1 ),
    COUNTROWS ( 'Table' )
)

Finally you can put them into pie visual and it will give you what you want.

Best Regards

Yilong Zhou

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

[Rupak_bi]

Hi @Stuart95051 

Just unpivot all columns except the first one from power query. 

[Stuart95051]

Thanks for the idea!  My horizontal totals and vertical totals are all 100%.  I think it has something to do with my data type (integer).  Let me convert them to true/false and see what happens.