Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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

 

DataInsights_0-1604440251661.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

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:
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

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

DataInsights
Super User
Super User

@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

 

DataInsights_0-1604440251661.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.