Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
179 | |
86 | |
69 | |
63 | |
55 |