cancel
Showing results for
Did you mean:
Helper II

## Dax Measure for Conditional column

Hi All,

I have the following data.

 KPI Actual Country Month KPI1 100 A 1 KPI1 200 B 1 KPI1 300 C 1 KPI1 400 D 1 KPI1 500 E 1 KPI1 50 A 2 KPI1 100 B 2 KPI1 150 C 2 KPI1 200 D 2 KPI1 250 E 2 KPI2 10 A 1 KPI2 11 B 1 KPI2 12 C 1 KPI2 13 D 1 KPI2 14 E 1 KPI2 A 2 KPI2 11 B 2 KPI2 12 C 2 KPI2 13 D 2 KPI2 14 E 2

I have to  get the KPI1/KPI2 vaue for Actual Country wise.

For Ex : for Country B :  (200+100)/11+11  = 13.63  (Sum of Month 1&2 for Country B for KPI1/Sum of Month 1&2 for Country B for KPI2)

But If you see , KPI2, Month 2, Country A -> Does not have data. So while computing for Country A, since KPI2 i no havng data for Month 2, KPI1 for month2 Should also NOT be considered.
ex: For Country A : (100+blank)/(10+blank) =10

Inorder to calculate with this logic Iam planning to create a columm with comparing KPI1 and KPI2 values .  Removng the 2n month value for KPI1 also

 Actual_New 100 200 300 400 500 100 150 200 250 10 11 12 13 14 11 12 13 14

Can someboy help on this?

1 ACCEPTED SOLUTION
Solution Sage

Try these measures:

Total Actual = SUM ( KPITable[Actual] )

KPI Calc =
VAR vCountry =
MAX ( KPITable[Country] )
VAR vKPI2Table =
FILTER (
ALL ( KPITable ),
KPITable[Country] = vCountry
&& KPITable[KPI] = "KPI2"
&& NOT ISBLANK ( KPITable[Actual] )
)
VAR vKPI2Months =
SELECTCOLUMNS ( vKPI2Table, "Month", KPITable[Month] )
VAR vNumerator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI1", vKPI2Months )
VAR vDenominator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI2" )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult

Note: assuming the example of Country A should be (100+blank)/(10+blank) = 10

3 REPLIES 3
Microsoft

Hi @Jimy123 ,

According to my understand, if the Actual value is blank, then all Actual values of the same country and month are blank, right?

You could use the following formula after adding a CountryMonth column to get the new Actual values:

new Actual Measure =
VAR _BlankCountryMonth =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), ISBLANK ( 'Table'[Actual] ) ),
'Table'[CountryMonth]
)
VAR _Actual =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[CountryMonth] IN _BlankCountryMonth ),
'Table'[Actual]
)
RETURN
IF (
MAX ( 'Table'[Actual] ) IN _Actual
&& MAX ( 'Table'[CountryMonth] ) IN _BlankCountryMonth,
BLANK (),
MAX ( 'Table'[Actual] )
)
KPI1/KPI2 =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Country] = MAX ( 'Table'[Country] )
&& 'Table'[KPI] = "KPI1"
),
[new Actual Measure]
)
/ SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Country] = MAX ( 'Table'[Country] )
&& 'Table'[KPI] = "KPI2"
),
[new Actual Measure]
)

My final output looks like this:

Here is the pbix file.

Best Regards,
Eyelyn Qin

Super User III

Hi @Jimy123

1. Place Country in a visual table

2. Create this measure and place it in the visual:

Measure =
VAR auxT_ =
FILTER (
Table1,
VAR aux_ =
IF ( Table1[KPI] = "KPI1", "KPI2", "KPI1" )
RETURN
NOT ISBLANK (
CALCULATE (
DISTINCT ( Table1[Actual] ),
ALLEXCEPT ( Table1, Table1[Country], Table1[Month] ),
Table1[KPI] = aux_
)
)
&& NOT ISBLANK ( Table1[Actual] )
)
RETURN
CALCULATE (
DIVIDE (
CALCULATE ( SUM ( Table1[Actual] ), Table1[KPI] = "KPI1" ),
CALCULATE ( SUM ( Table1[Actual] ), Table1[KPI] = "KPI2" )
),
auxT_
)

3. looks like there's a mistake in your calculation or country A. Should be (100+blank)/(10+blank)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Solution Sage

Try these measures:

Total Actual = SUM ( KPITable[Actual] )

KPI Calc =
VAR vCountry =
MAX ( KPITable[Country] )
VAR vKPI2Table =
FILTER (
ALL ( KPITable ),
KPITable[Country] = vCountry
&& KPITable[KPI] = "KPI2"
&& NOT ISBLANK ( KPITable[Actual] )
)
VAR vKPI2Months =
SELECTCOLUMNS ( vKPI2Table, "Month", KPITable[Month] )
VAR vNumerator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI1", vKPI2Months )
VAR vDenominator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI2" )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult

Note: assuming the example of Country A should be (100+blank)/(10+blank) = 10

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!