Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
krogsgaard99
Regular Visitor

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!

385551475_733750554863443_3434724336156760929_n.png387451765_1032061997845371_6120249825539575281_n.pngBest regards, Lucas

1 ACCEPTED SOLUTION

Hi @krogsgaard99 ,

 

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.

vtangjiemsft_0-1702278456252.png

vtangjiemsft_1-1702279489567.png

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. 

 

 

View solution in original post

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

Hi @krogsgaard99 ,

 

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.

vtangjiemsft_0-1701915455303.png

 

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 @v-tangjie-msft,
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.

krogsgaard99_0-1701975068361.png
Then when I click on week 2 - it shows more than 13 days and it should only show day 8 to 14. 

krogsgaard99_1-1701975116199.png

I have attached the new PBIX with the changes I have made: https://we.tl/t-SzDQFTlPuX

I hope this clarifies my problems.

Hi @krogsgaard99 ,

 

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.

vtangjiemsft_0-1702278456252.png

vtangjiemsft_1-1702279489567.png

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. 

 

 

krogsgaard99
Regular Visitor

krogsgaard99_0-1701763947912.png

krogsgaard99_1-1701764220517.pngkrogsgaard99_2-1701764255938.pngkrogsgaard99_3-1701764284893.png

krogsgaard99_4-1701764416099.png

 

 

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.

amitchandak
Super User
Super User

@krogsgaard99 , 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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.