cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Dax Measure for Conditional column

Hi All,

 

I have the following data.

 

KPIActualCountryMonth 
KPI1100A1
KPI1200B1
KPI1300C1
KPI1400D1
KPI1500E1
KPI150A2
KPI1100B2
KPI1150C2
KPI1200D2
KPI1250E2
KPI210A1
KPI211B1
KPI212C1
KPI213D1
KPI214E1
KPI2 A2
KPI211B2
KPI212C2
KPI213D2
KPI214E2

 

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
Super User II
Super User II

@Jimy123,

 

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

 

DataInsights_0-1604440251661.png

 

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

View solution in original post

3 REPLIES 3
Microsoft
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:
11.6.2.1.PNG

 

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

Super User III
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 

 

SU18_powerbi_badge

Super User II
Super User II

@Jimy123,

 

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

 

DataInsights_0-1604440251661.png

 

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

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors