Solved: Assign a value to a letter to calculate a KPI - Page 2

o59393 · ‎04-17-2020

Hi all

I have assigned a value to a letter creating a table like this:

SGP Value

C	10
NA
O	5
R	0

In case of the NA, should not be considered for the KPI.

The KPI desired is simple:

C= (Total count of C * 10) / 
(Total count of C * 10) + (Total count of O * 5) + (Total count of R * 0)

O= (Total count of O * 5) / 
(Total count of C * 10) + (Total count of O * 5) + (Total count of R * 0)

R= (Total count of R * 0) / 
(Total count of C * 10) + (Total count of O * 5) + (Total count of R * 0)

total KPI = (Total count of C * 10) + (Total count of O * 5 + (Total count of R * 0) 
/ 
(Total count of C * 10) + (Total count of O * 10) + (Total count of R * 10)

My dax is traduced into this (apologies if is not an efficient formula):

SGP KPI = 

var C = 

CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") / 

( CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="O") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="R") )


var O = 

CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="O") / 

( CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="O") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="R") )


var R = 

CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="R") / 

( CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="O") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="R") )


var total_KPI = 

(
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="O") + CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="R") 
)
/ 

( CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="C")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="O")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") + 
CALCULATE(COUNTROWS(Sheet3),Sheet3[SGP]="R")*CALCULATE(SUM('Value'[Value]),'Value'[SGP]="C") )

return 

total_KPI

The 2 problems I have are the following,

The dax is returning to NA a value and I want it to be blank. The second problem is that I dont know how to return the respective value of C, R, O, NA and total KPI value for each letter in the table below:

Can you please tell me how to get it right?

I attach excel with datasource and sheet "Result desired" explains the calcualtion with the result.

Also attach pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhl1dea00ulywwf4b?e=ZhRah3

Thanks!

v-kelly-msft · ‎04-21-2020

Hi @o59393 ，

As I didnt add a default value for KPI,you can add "total" value as a default value,just modify the dax expression as below:

KPI = 
var _total=CALCULATE(SUM('Table (2)'[Count of value])*10,FILTER('Table (2)','Table (2)'[Row labels]="C"))+CALCULATE(SUM('Table (2)'[Count of value])*5,FILTER('Table (2)','Table (2)'[Row labels]="O"))+CALCULATE(SUM('Table (2)'[Total])*0,FILTER('Table (2)','Table (2)'[Row labels]="R"))
VAR C_percent= DIVIDE(CALCULATE(SUM('Table (2)'[Count of value])*10,FILTER('Table (2)','Table (2)'[Row labels]="C")),_total)
var O_percent=DIVIDE(CALCULATE(SUM('Table (2)'[Count of value])*5,FILTER('Table (2)','Table (2)'[Row labels]="O")),_total)
var R_percent=DIVIDE(CALCULATE(SUM('Table (2)'[Count of value])*0,FILTER('Table (2)','Table (2)'[Row labels]="R")),_total)
var _subtotal=CALCULATE(SUM('Table (2)'[Count of value])*10,FILTER('Table (2)','Table (2)'[Row labels]="C"))+CALCULATE(SUM('Table (2)'[Count of value])*10,FILTER('Table (2)','Table (2)'[Row labels]="O"))+CALCULATE(SUM('Table (2)'[Count of value])*10,FILTER('Table (2)','Table (2)'[Row labels]="R"))
var total_percent=DIVIDE(_total,_subtotal)
Return
SWITCH('Table (2)'[Row labels],"C",FORMAT(C_percent,"percent"),"O",FORMAT(O_percent,"percent"),"R",FORMAT(R_percent,"percent"),"Total",FORMAT(total_percent,"percent"),FORMAT(total_percent,"percent"))

And you will see: