Forum Discussion
Service Level Issue - Days Not Showing Correct Result
Hello Power BI Community, I am facing an issue with a Power BI graph visual that involves service level calculations.
Here's a brief overview of the problem: I have successfully calculated the service level and created a graph visual that displays the weeks of January. The initial results are correct. However, when I click on the weeks, the days do not show the right result. I've created two columns:
ReleasedAfterCutoff = IF(TIME(HOUR('Pick detail report (7)'[Action created at (time)]), MINUTE('Pick detail report (7)'[Action created at (time)]), SECOND('Pick detail report (7)'[Action created at (time)])) < TIME(16, 15, 0), 1, 0)
IsLateDespatch = IF(TIME(HOUR('Despatch summary (7)'[Despatch date (time)]), MINUTE('Despatch summary (7)'[Despatch date (time)]), SECOND('Despatch summary (7)'[Despatch date (time)])) > TIME(16, 30, 0), 1, 0)
And the measures I'm using:
TotalOrdersReleased = CALCULATE(COUNTROWS('Pick detail report (7)'), 'Pick detail report (7)'[ReleasedAfterCutoff] = 0)
TotalLateDespatches = CALCULATE(COUNTROWS('Despatch summary (7)'), 'Despatch summary (7)'[IsLateDespatch] = 1) ServiceLevel = DIVIDE([TotalOrdersReleased] - [TotalLateDespatches], [TotalOrdersReleased], 0)
I need help in understanding why the days are not showing the correct results when I click on the weeks in the graph visual. Any insights or suggestions on how to troubleshoot and resolve this issue would be greatly appreciated. Thank you in advance for your assistance!
Best regards, Lucas
Hi Anonymous ,
You can create a measure.
TotalLateDespatchs = COUNTROWS(FILTER('Despatch summary (7)','Despatch summary (7)'[IsLateDespatch] = 1))Flag = IF([ServiceLevel]=1,0,1)Then talk about [Flag=1] placed on the visual object filter.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous , I Hope TotalOrdersReleased, TotalLateDespatches are measures. Try with this change
TotalOrdersReleased = CALCULATE(COUNTROWS('Pick detail report (7)'),Filter( 'Pick detail report (7)' 'Pick detail report (7)'[ReleasedAfterCutoff] = 0))
TotalLateDespatches = CALCULATE(COUNTROWS('Despatch summary (7)'), Filter('Despatch summary (7)', 'Despatch summary (7)'[IsLateDespatch] = 1) )
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.I recently discovered that I can send you a PBIX file using WeTransfer, so here's the link: https://we.tl/t-9v0iIOeYvL
Hello, Amit
I appreciate your response.
As you suggested, I attempted to modify the measures, but nothing changed.
As a result, I have deleted the confidential information and included screenshots of the Power BI tables.
The "despatch summary" table is shown in the first image, and the remaining images are from the "pick detail report" table.
I hope I have given you what you asked for. If not, do let me know what you require.Hi Anonymous ,
Update measures.
TotalLateDespatchs = COUNTROWS(FILTER(ALLSELECTED('Despatch summary (7)'),'Despatch summary (7)'[IsLateDespatch] = 1))TotalOrdersReleased = COUNTROWS(FILTER('Pick detail report (7)','Pick detail report (7)'[ReleasedAfterCutoff] = 0))Then the result is as follows.
If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
Thanks for the answer!
I updated my measures as you described and I changed service level measure from: ServiceLevel = DIVIDE(([TotalOrdersReleased] - [TotalLateDespatchs]), [TotalOrdersReleased])
To:
ServiceLevel = 1-DIVIDE([TotalLateDespatchs],DISTINCTCOUNT('Pick detail report (7)'[Sales order number]))
As I wanted to see the service level from the total orders released and not on the orders released after cut-off.
Allow me to describe the desired outcome. The graph shows six weeks, even though there should only be four. The total service level should be 99.58%, according to the card visual in the bottom left corner, but when I look at the graph, all of the weeks have less than 99%, which makes no sense to me.
Then when I click on week 2 - it shows more than 13 days and it should only show day 8 to 14.I have attached the new PBIX with the changes I have made: https://we.tl/t-SzDQFTlPuX
I hope this clarifies my problems.Hi Anonymous ,
You can create a measure.
TotalLateDespatchs = COUNTROWS(FILTER('Despatch summary (7)','Despatch summary (7)'[IsLateDespatch] = 1))Flag = IF([ServiceLevel]=1,0,1)Then talk about [Flag=1] placed on the visual object filter.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
