Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 !!
Proud to be a PBI Community Champion
@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 !!
Proud to be a PBI Community Champion
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |