DAX Help to calculate Percentage for Pie Chart

[AnkitaaMishra]

Hi,

I am trying to visualize % of Product Performance using a Pie Chart and below is the sample data.

ID	Product Name	Tie %	Product %
100	A	9.68	40.32258
100	B	9.68	50
101	C	5.4	31.48714
101	D	5.4	63.11385
102	E	8.06	45.16129
102	F	8.06	46.77419
103	A	3.92	29.8287
103	B	3.92	66.25337
104	C	10	45
104	D	10	45
105	E	9.1	80.17398
105	F	9.1	10.7304
106	A	1.69	44.90657
106	B	1.69	53.40113

So for each ID, I am trying to plot Product % but its not summing up to 100%. 
for example : if i consider ID = 100 then for product A and B, the Product % is 40.32258%+50% = 90.32258%. 

Now if i add the Tie % column to above calculation then it 90.32258%+9.68% = 100%. 
I cannot use both the columns in Values field of Pie Chart.
The expected output is given below : 

How can I achieve this?

[Anonymous · Accepted solution]

Thank you for your prompt reply! @amitchandak

Hi @AnkitaaMishra 

Based on your screenshot, your table structure should look like this:

If you don't want to change your table structure, I can offer you a workaround, but it won't fully meet your needs.

First, create an index column in power query.

Then, create a measure by using the following DAX:

Changed Tie % = 
VAR _ID = SELECTEDVALUE('Table'[ID])
VAR _index = SELECTEDVALUE('Table'[Index])
RETURN
IF(CALCULATE(SELECTEDVALUE('Table'[Tie %]),FILTER(ALL('Table'),'Table'[ID]=_ID&&'Table'[Index]=_index-1))<>BLANK(),BLANK(),SELECTEDVALUE('Table'[Tie %]))

Place the corresponding fields in the pie chart:

Result:

Best Regards,

Jayleny

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

[AnkitaaMishra]

hi @Anonymous ,

Thanks for your response! this workaround works for me.🙂

[amitchandak]

@AnkitaaMishra , Have three measures and use them in pir with no legend Assuming Product % and Tie% are measures

A= calculate([Product %], filter(Table, Table[Product Name] = "A") )

B= calculate([Product %], filter(Table, Table[Product Name] = "B") )

Then use Tie % as thrid Meausre

[AnkitaaMishra]

Hi @amitchandak , 

Thanks for your response!
the products are different for each ID, so how can below part execute dynamically : 
filter(Table, Table[Product Name] = "A")
also, the legend is needed to know which product are involved in the ID.