cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

work out percentage by previous day

Hi, 

 

I'm new to Power Bi and I'm having a problem with a calculation that I'm trying to do. 

 

At the end of a shift the team leader fills in a MS form that I have created. We have a day shift and a night shift. When the data transfers to power Bi I have two rows of data for that day (day and night shifts).

 

I'm trying to get power bi to add the two rows of "A domes" together then divide them by the two "Domes Passed" rows to give me the percentage of "A domes" that have passed from the previous day.

 

So in the table below I would like power bi to add the data from the 29 June 2020 like this;

 

A domes = 83 + 104 = 87

Domes Passed = 142 + 201 = 343

so the calculation would be 87/201*100 = 43.28%

 

Capture.PNG

 

I hope that makes sense. 

 

Thanks 

 

Graham 

 

 

 

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: work out percentage by previous day

Can you double check your math and explain again? 83+104 = 187 (you have 87) and why are we dividing by 201 (not 343)? Do we always look for the Nights shift?

Do you have a DimDate table? https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Solution Supplier
Solution Supplier

Re: work out percentage by previous day

Hi Graham,

 

Try this:

 

A Domes % Passed =
VAR Numerator =
SUM ( ShiftData[A Domes] )
VAR Denominator =
SUM ( ShiftData[Domes Passed] )
VAR Result =
DIVIDE ( Numerator, Denominator )
RETURN
Result
 
---
 
Use this measure in a matrix with Date as either the rows or columns.
 
There's an error in your calculation: 83 + 104 = 187.
 
Hope this helps.
Highlighted
Super User VII
Super User VII

Re: work out percentage by previous day

@GrahamP41 I think @AllisonKennedy  asked a valid question, first math is wrong, and 2nd does the denominator suppose to be just night shift or sum of both the shifts.

 

You have to check your calculation before posting your question, it helps community to provide a solution rather than spending time on clarifications. Just a friendly recommendation.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
New Member

Re: work out percentage by previous day

Sorry Team, 

 

I didnt realise my maths was so wrong, thats what I get by trying to do this post with a 3yr old running wild. 

 

The formula needs to be with both the sum of day and night shift. 

 

So 83+104 = 187 for A domes, 

and 142 + 201 for domes passed

 

Result = 187 / 343 * = 54.51

 

Again really sorry for the mistake. 

 

 

Highlighted
Super User VII
Super User VII

Re: work out percentage by previous day

@GrahamP41 this makes more sense, thanks for clarifying. As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

 

The measure is already provided by @DataInsights I will add date dimension, set relationship between tables, and then on the visuals, use date from date dimension, and measure. 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors