work out percentage by previous day

[GrahamP41]

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%

I hope that makes sense. 

Thanks 

Graham 

[DataInsights]

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.

[parry2k]

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

[GrahamP41]

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. 

[parry2k]

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

[AllisonKennedy]

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