Assign a value to a letter to calculate a KPI

[o59393]

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!

[parry2k]

@o59393 do you want this?

[o59393]

Hi @parry2k 

Seems good, but the total should be 90.91% 

Thanks for the support!

[parry2k]

@o59393 not sure how you get to 90.91%

[o59393]

Hi @parry2k 

You can download the excel I attached in the post and look the tab "Result desired", but pretty much is:

Thanks!

[parry2k]

@o59393 what is the logic to multiple with 10 (B8 x 10 )

[o59393]

B8 x 10 is the best possible result that could be achieved.

Thanks 🙂

[v-kelly-msft]

Hi @o59393 ,

I have a workarond you,as the "total" value has a different logic from other lines, so it cant be simply calculated by sum of the column,thus, you'd better add a row called "total",and make your logic in the calculation,pls see below:

It is an expression for a calculated column:

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"))

Finally ,you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[o59393]

Hi @v-kelly-msft 

Thanks for the help!  

I wanted to create a ring chart witht the C,R,O and NA and a Card with the total but I got this:

Can it be a dax measure instead of calculated column? And have by default the total in the card? and in the ring chart just the C, NA, O and R?

Thanks.

[v-kelly-msft · Accepted solution]

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:

For the updated .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[o59393]

Thank you @v-kelly-msft it worked 🙂

I also tried a dax measure and it worked, have a look:

SGP KPI = 

var C = 

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

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


var O = 

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

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


var R = 

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

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


var total_KPI = 

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

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

return
SWITCH(
 TRUE(),
    HASONEVALUE('Suppliers Compliance'[SGP]) && SELECTEDVALUE('Suppliers Compliance'[SGP]) = "C", C,
    HASONEVALUE('Suppliers Compliance'[SGP]) && SELECTEDVALUE('Suppliers Compliance'[SGP]) = "O", O,
    HASONEVALUE('Suppliers Compliance'[SGP]) && SELECTEDVALUE('Suppliers Compliance'[SGP]) = "R", R,
    total_KPI
)

Thanks!