Daily Average is for same customer is showing different values

DAX DAX Calculations
1

Hello Everyone,

I am calculating and tracking the production performance of 5 manufacturing plants using Power BI

I have attached Power BI file and a PPT (where the particular customer is highlighted)

For the month of Aug 18 in case of a particular customer there are two different average (one shown in table and other shown in card). The two different average is because of change in the number of working days

For the average shown in table - the average is calculated based on the number of days the product is actually manufactued
For the average shown in the card - It takes all the working days

Why is this difference. I want the average data in the table to take all the working days.

Can you check my PBI file and help me with your views.

ThanksPBI Average Query.pptx (192.7 KB)
CurrentMonthPerformance.pbix (1.6 MB)

2

Seems like the issue is working days. When the context is on BMW it shows 23 working days but when it’s on the entire report it shows 24 working days.

It may be because BMW may just have 23 working days in the current context. Working%20Days

3

Dear D’Souza,

I know this is because of the working days. But I want to take all the working days when average is calculated in the table.

In the PPT that I had sent if you look for BMW there are two averages - One in table and other one in Card.

To summarize, is it possible to consider all the working days to compete the average irrespective of whether that customer is manufactured in that day or not.

In this case is it possible to change the number of working days to 24 working days for BMW in the table.

4

I’ve looked into it and to me it looks like it’s just not comparing like for like.

The P5 avg. per day has a numerator of [P5 Output]

P5 Avg Per Day = DIVIDE( [P5 Output], [P5 WorkingDays], 0)

But then when you work your way through the BMW formula the numerator is different, it’s not P5

BMW AVG Per Day = DIVIDE( [BMW Output], [P5 WorkingDays], 0)

BMW Output = 
CALCULATE( [Total Output], PartNoCustomerMaster[Customer] = "BMW" )

Total Output = SUM( PRODF19[Qty in Un. of Entry] )

So to me it’s numerator is total output?

As I’m not familiar with the data I’m not 100% sure but potentially this need to be some type of P5 output to be like for like comparison.

Power BI will never calculate something incorrectly so it will always be to do with something in the branches of measure or some different context on the calculation that you didn’t quite realise. These are usually the problems.

5

Yes. Using the ALL function. See below for the change to the P5 Working Days formula. I figure no columns in your PartNoCustomerMaster table should affect the working days measure.

P5 WorkingDays = 
IF( ISBLANK( [P5 Output] ), BLANK(),  
CALCULATE( 
    DISTINCTCOUNT( PRODF19[Posting Date] ), 
    Dates[P5 Working Day] = "Working Day",
    ALL(PartNoCustomerMaster)) 
)
6

Yes.
It works. Many Thanks