Dynamic denominator with calculated columns

[Anonymous]

Hi All,

New to Power BI and looking to rebuild a scorecard I have currently in Excel in PBI.  Not all of our vendors operate in all of our lines of business so I'm looking to create a dynamic formula to calculate the # of KPI each vendor should be held accountable for & this serves as the denominator for what we call the complaince score (# of KPI above goal / # of KPI in scope).

The % columns come straight from the data source where each row is a seperate line of data.  The goal column was created by using the "New Measure" button and entering a formula - for example the 3rd goal column has a formula of IF(ISNUMBER('CSOT - PBI dash (2)'[Num Carrier Misses divided by 1 - CSOT Shipment #]),IF('CSOT - PBI dash (2)'[Num Carrier Misses divided by 1 - CSOT Shipment #]<.05,1,0),"")

My initial trial was to use COUNT but that function only accepts a column reference so the formula errors out.  Any suggestions. 

[pranit828]

Hi @Anonymous 

Compliance = 
VAR TAC = IF(ISNUMBER('CSOT - PBI dash (2)'[Tac%]),IF('CSOT - PBI dash (2)'[TAC%]<.05,1,0),0)
VAR Drop= IF(ISNUMBER('CSOT - PBI dash (2)'[Drop%]),IF('CSOT - PBI dash (2)'[Drop%]<.05,1,0),0)
VAR CSOT= IF(ISNUMBER('CSOT - PBI dash (2)'[CSOT%]),IF('CSOT - PBI dash (2)'[CSOT%]<.05,1,0),0)
VAR IOT = IF(ISNUMBER('CSOT - PBI dash (2)'[IOT%]),IF('CSOT - PBI dash (2)'[IOT%]<.05,1,0),0)
Return TAC + Drop + CSOT + IOT

The above measure should help you with the baseline of what you need.

Hope it helps. 

[Seanan]

Hi @Anonymous 

Would you be able to send an example of your dataset?