% total in a pie chart, can be done per row only?

[rickylee]

Hello,

I have the following table in database.

Application	Stage1	Stage2	Stage3
A	01/09/2018	02/09/2018	03/09/2018
B	01/09/2018	03/09/2018	07/09/2018

I added two columns to find out the hours it take to pass each stage:

The formula of the new added columns:
Stage1 -> Stage2 = DATEDIFF(Sheet1[Stage1],Sheet1[Stage2],HOUR)

Stage2 -> Stage3 = DATEDIFF(Sheet1[Stage2],Sheet1[Stage3],HOUR)

Then, I display the information in a table and a pie chart like this:


Then, I select row 1, application A, to see the % of the pie chart:

My problem arised in here: I expected the chart to be 50% (Stage1 -> Stage2) and 50% (Stage2->Stage3) because each stage takes 24 hours. The maths should be 24 /(24+24) = 50%

However, I think power bi adds up the total of both stages I.e. So "Stage1 -> Stage2" is 24/(72+120) = 12.5%.

This is not what I need. Is there a solution for this?

Thanks,
Ricky 

[v-jiascu-msft]

Hi Ricky,

You also can use a slicer. Please refer to the snapshot below.

Best Regards,
Dale

[PattemManohar · Accepted solution]

@rickylee I've tried below steps to solve your scenario..

Please do the additional calculation columns in "Power Query Editor" using custom column as below

Duration.TotalHours([Stage2]-[Stage1])

Duration.TotalHours([Stage3]-[Stage2])

Now the table looks like this...

Now, unpivot all the fields apart from the "Application" field and it will look like this

Now use the "Matrix" visual and Pie-Chart and the output looks like this...

But in the pie-chart, you need to have visual level filters to have only "Stage1toStage2" and "Stage2toStage3" attributes only.

Hope this helps !!