YTD Average - DAX calculation

[Anonymous]

Dear all,

I'm trying to calculate the YTD Average value - but I would need your help on this KPI & DAX language.

Data are like this:

-> Conformity level (Grade A = OK/NON OK per scan/factory/etc.).

So I would need to calculate the average of grade A/period and then make the average from the beginning of this year until a specified period (ex. P5 - or 31st May).

Here some data:

year	month	day	CONFORMITY	Period	Factories
2021	4	13	OK	P04	RCR
2021	4	13	OK	P04	RCR
2021	5	6	OK	P05	RDM
2021	5	6	OK	P05	RDM
2021	5	6	OK	P05	RDM
2021	5	6	OK	P05	RDM
2021	5	6	OK	P05	RDM
2021	4	16	OK	P04	RCC
2021	4	16	OK	P04	RCC
2021	4	18	OK	P04	RCC
2021	4	18	OK	P04	RCC
2021	5	3	OK	P05	RCC
2021	5	4	OK	P05	RCC
2021	4	1	OK	P04	RGU
2021	4	1	OK	P04	RGU
2021	4	1	OK	P04	RGU
2021	4	1	OK	P04	RGU
2021	4	1	CTRL_NOK	P04	RGU
2021	4	1	OK	P04	RGU
2021	4	12	WRNG_NOK	P04	RGU
2021	4	12	WRNG_NOK	P04	RGU
2021	4	12	OK	P04	RGU
2021	4	12	OK	P04	RGU
2021	4	12	OK	P04	RGU
2021	4	12	OK	P04	RGU
2021	4	29	OK	P05	RGU
2021	4	29	OK	P05	RGU
2021	4	29	OK	P05	RGU
2021	4	29	OK	P05	RGU
2021	4	29	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	WRNG_NOK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	WRNG_NOK	P05	RGU
2021	5	14	OK	P05	RGU
2021	5	14	OK	P05	RGU

I would really appreciate your help.

Best regards

Hervé

PS: @v-kelly-msft 

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CONFORMITY]="OK")),COUNTROWS('Table'))

[Syndicate_Admin]

Good morning, I press my name is pablo and I am from Argentina and I have a problem to get the average talk time of a telephone exchange, AHT calls, if I on the excel calculate with the average function that value gives me correctly but on power bi I do not find the turn, what I do is calculate the total time of conversation but I do not find the way to get that average, I attach a capture of what I have and if anyone can help me thank you very much.-

Best regards;

Pablo

[ryan_mayu]

@Syndicate_Admin 

pls see the attachment below

[ryan_mayu]

@Anonymous 

could you pls provide the expected output?

[Anonymous]

Hello,

Expected output is the average of Grade A (Conforme products) / period. (ex. YTD -> From P1 (beginning of the year) until P4 (or now), the average of grade A from P1 to P4 (or now)).

Hope I'm clear

Thanks

Hervé

[ryan_mayu]

@Anonymous 

could you pls provide the real output based on the sample data you provided? It will be better if you can provide the calculation logic as well.

[Anonymous]

Answer should be 88,37 % (average of grade A from P4 & P5).

(Total count OK/(OK + Warning + Non OK)*100

[ryan_mayu · Accepted solution]

@Anonymous 

pls try this

Measure = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CONFORMITY]="OK")),COUNTROWS('Table'))

[Anonymous]

Thanks a lot for you help. Can we specify in this formula, that I would need the % for this year (2021)?

Then I can make the same calculation for 2020 (it will be my objective) and build a KPI.

Best regards

Hervé

[ryan_mayu]

@Anonymous 

you can try this and change 2021 to 2020 to get 2020KPI

Measure = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CONFORMITY]="OK"&year=2021)),CALCULATE(COUNTROWS('Table'),FILTER('Table',year=2021)

[Anonymous]

Hello

Thanks for your reply.

I tried the formula but I have an error:

Do you know why?

Thanks

Hervé

[ryan_mayu]

@Anonymous 

sry i missed on & symbol, && is AND function

Measure = DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[CONFORMITY]="OK"&&year=2021)),CALCULATE(COUNTROWS('Table'),FILTER('Table',year=2021)

[Anonymous]

Hello

Sorry - still a mistake:

Thx

Hervé

[ryan_mayu]

@Anonymous 

is year your column name? based on your sample data, year column is your first column .pls change to your real column name.

[Anonymous]

Strange - yes, my column name is "year"

[ryan_mayu · Accepted solution]

@Anonymous 

pls change year to rclms_qa[year]

[Anonymous]

And I have another question to complexify a bit.

Can you the created formula:

YTD 2021 = Divide(CALCULATE(COUNTROWS(rclms_qa), FILTER(rclms_qa,rclms_qa[CONFORMITY]="OK"&&rclms_qa[Mars Year]=2021)),CALCULATE(COUNTROWS(rclms_qa), FILTER(rclms_qa,rclms_qa[Mars Year]=2021)))*100

And add some DAX elements to get the results of my last 13 periods?

Thanks

Hervé

[Anonymous]

Thanks a lot for your help - it works