cancel
Showing results for
Did you mean:
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%

I hope that makes sense.

Thanks

Graham

5 REPLIES 5
Highlighted
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.

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

excelwithallison.com

Highlighted
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

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

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

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

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

Announcements

#### Microsoft Ignite

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

#### 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
Top Kudoed Authors