Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

Assign a value to a letter to calculate a KPI

Hi all

 

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

 

SGP  Value

C10
NA 
O5
R0

 

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:

 

sgp total.PNG

 

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!

 

1 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:

 

Annotation 2020-04-22 095217.png

 

For the updated .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@o59393 do you want this?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

Seems good, but the total should be 90.91% 

 

totlas2.PNG

 

Thanks for the support!

 

 

@o59393 not sure how you get to 90.91%



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

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

 

excel1.PNG

 

Thanks!

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

Thanks 🙂

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:

 

Annotation 2020-04-20 150629.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

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:

 

asas.PNG

 

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.

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:

 

Annotation 2020-04-22 095217.png

 

For the updated .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

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
)

 

 

sgp111.JPG

 

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.