Collaps group in pivot lead to wrong values with Measures created

[hansolu]

Hi,

i am working with powerquery to clean up some oven records. finally i get a list which looks like the attached file.
I load the result into the datamodel and want to calculate the usage per day per oven (=OEU) by adding some measures.
In total I created 4 Measures leading to the OEU which I need.

Calculation should be:
Per oven:
e.g. one oven is running 12 hours, usage per day is 12/24 = 0.5

daily usage is "Total running hours for all ovens" / (8*24) as i have 8 ovens in total and i always want to compare to all ovens per day

I run into two issues now.

1st problem: The daily numbers are correct, but when I group the pivot table to e.g. week the summary of the week is wrong. It shows the summary of the whole selection instead!
2nd Problem: Daily usage only show the usage for the oven actually running, not for all 8 ovens.

Where is my failure? I guess I need to use the Measures as I want to sum each single row and based on this do the calculation of the OEU

thanks for your help

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[hansolu]

Hi

sorry, i just changed the title. Sorry, will be doing better next time

br
Hansolu

[JeteMc]

Thank You for changing the title.
If I understand then perhaps the following will help.
1. Produce an unpivoted table as modeled in Table1_2 on the PQ Table sheet using the following M Code in the Power Query advanced editor:

Please Login or Register  to view this content.

2. A pivot table was produced using the new table
3. Date (grouped into 7 days intervals) is in the Rows area
4. Oven is in the Columns area
5. Time (hrs) is used twice in the Values area, with the second usage being percentage of row total
Let us know if you have any questions.

[hansolu]

Hi
thanks for the reply. This is one option but not the one I am looking for.
Let me try to explain a bit deeper.

If i use the calculation percentage of row total I do get a wrong value. The value should be e.g.
8 oven times 24 hours per day times 7 days a week = 1344 total hours available. % usage should be how much of these hours the oven where running. So if i only use 1000 hours the percentage in total is 1000/1344 = 74%

Now I need to dive in and see why the usage is only 74%. This could be because i did not use one oven at all or all ovens did a bad performance.
So I want to drill down and see the % usage per oven in this period. Then I want to drill down and see the % usage per day per oven.

I used two different PQ to show one time the weekly performance and then a second PQ to show the performance by day and oven. But i want to combine and only use one final Pivot table where I can choose the filter or collaps/expand to show my period/oven of interest.

So in my opinion I need to summarize the duration per oven and day based on the selection and then divide to the total time available for the days and selected ovens. One oven has 24 hours availability per day.
Thats why i used the DAX formula but as soon as i collaps the week Excel show the total numbers for the whole column for all periods available and not only the values for the collapsed period.

I hope its more clear what i want.

thanks

[JeteMc]

I believe that I have an answer for question #1 (post #1)
Modify the Duration per day and oven measure to read: =SUM([Duration])
As it is context driven it will show either the sum per day when the week is expanded or sum per week when the week is collapsed (i.e. oven 3 is used 79.24 hours in week 50).
As for question #2 I feel as if you will have to fill in every day of the week and give a value to every oven in order to get the OEU to calculate correctly (see rows 107 and 142 on the raw data sheet).
Let us know if you have any questions.

[hansolu]

Hi,

thanks for the ideas. I now did some other steps.
Create a calendar table
create a oven table and append it to the Calendar table
Then use the actuals and merge it with the previous table
Then in the datamodel use the DAX formula to show the OEU.
Seems to work, but a bit of a work to do in the first place.

Maybe i am still able to improve a bit

thanks