Dax Measure for Conditional column

[Anonymous]

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?

[Anonymous]

Hi @Anonymous ,

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.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[AlB]

Hi @Anonymous 

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 

[DataInsights · Accepted solution]

@Anonymous,

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