Column Series / Legend as a Percent of Column Itself?

[ttam110]

Hi,

In the screenshot below, I'm trying to overlay "Persona" penetration in a particular product. 

So for instance, on the axis I have the Product's penetration (e.g. % of grand total) but in the Legend / Column Series field, I have the Persona. Unfortunately. the percentages come up in the graph as follows: 

1.  % = (Total Product 1, Persona A) / (Total Product)

'm looking for...

1. % = (Total Product 1) / (Total Persona A)
2. % = (Total Product 1) / (Total Persona B)
3. % = (Total Product 1) / (Total Persona C)

Any ideas how I can achieve this? 

[ttam110]

Update: maybe this is a better represnetation https://i.imgur.com/QjtEXBa.png

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[ttam110]

Hi, here’s a link

https://www.dropbox.com/s/muv2a2rugmloujv/Dashboard%20Example.zip?dl=1

Here’s also a visual explanation of why I’m trying to accomplish: https://i.imgur.com/QjtEXBa.png

[Ashish_Mathur]

Hi,

It works fine.  Here is the result i got.  You may download my file from here.

[jesperlkjaer]

Thanks! Have spent hours solving this, and this finally worked!

[Ashish_Mathur]

You are welcome.

[jesperlkjaer]

Quick question. Is there a way to omit blanks? I have in one country where 50% of responses are "blanks" and I don't want them to be counted at all. 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[jesperlkjaer]

Hi again, 

I managed to fix it by using the ALLSELECTED and use filters to remove blanks. 

But thank you. 

[ttam110]

Wow, thanks -- this is what I was looking for. The only "issue" is that I lose the product penetration on the y-axis (% of customer grand total)

[Ashish_Mathur]

You are welcome.  What do you mean by "I lose the product penetration on the y-axis (% of customer grand total)"??

[ttam110]

On the y-axis, I wanted to display the % of total customers that were on that product. That is, SUM (Customers with product A) / SUM(All Customers).

Note that on your file you sent, the % on the y-axis are different than what I originally had.  Unfortunately I'm not sure if it is possible given the structurre that I am seeking -- but I really appreciate your help

[v-juanli-msft]

Hi @ttam110

It is not possible to add the data as your picture. Here is a workaround

Change to Line and stacked column chart.

Create measures

sum per product = CALCULATE(COUNT(Dup[Product]),ALLEXCEPT(Dup,Dup[Product]))

all product = CALCULATE(COUNT(Dup[Product]),ALL(Dup))

% of customer grand total = [sum per product]/[all product]

add [ % of customer grand total] to line values.

Best Regards

Maggie

[Ashish_Mathur]

Hi,

That is not possible.  If my previous reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

Something like this

=SUM(Data[Value])/CALCULATE(SUM(Data[Value]),ALL(Data[Product]))

Hope this helps.

[ttam110]

HI there,

I created that measure, but not sure how this would help? If I use it column values, the calculatations are not right...

Measure 2 = sum(Table[Customer ID])/CALCULATE(Sum(Table[Customer ID]),ALL(Table[Product]))

thanks for your time