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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GrahamP41
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
DataInsights
Super User
Super User

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.




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

Proud to be a Super User!




@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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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. 

 

 

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.