Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamic denominator with calculated columns

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. 

 

 

Need Help

2 Replies

  • Seanan's avatar
    Seanan
    Icon for Solution Supplier rankSolution Supplier
    4 years ago

    Hi Anonymous 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.