Need help with a measure

[Anonymous]

Hi All,

I got stuck in writing a measure, may be one of you can help.

I have two measure alreay created

PM cost Q1 2018
PM cost Q2 2018

I created an another measure which sums these two measure if we need for both the period.

PM cost 2018 = [PM Cost Q1 2018]+[PM Cost Q2 2018]

But when ever I am Filtering it on Quarter it gives me the total, that i understand because it's just a normal measure(It will give me the sum each time).

Then I tried something like this:

PM Cost 2018 Final = 
                      CALCULATE(
                      VAR IsQ1Selected = IF(ISFILTERED('Date'[Quarter]) && MAX('Date'[Quarter]) = "Q1" , True, FALSE)
                      VAR IsQ2Selected = IF(ISFILTERED('Date'[Quarter]) && MAX('Date'[Quarter]) = "Q2", True, False)
                      VAR Calc1 = [PM Cost Q1 2018]
                      VAR Calc2 = [PM Cost Q2 2018]
                      VAR Calc3 = [PM cost 2018]
                      RETURN IF(IsQ1Selected,Calc1,IF(IsQ2Selected,Calc2,IF(IsQ1Selected && IsQ2Selected,Calc3,0))
                
                ))

But this also not working, When I am selecting both the quarters it is only giving me the result for the Q2 only.

[v-piga-msft]

Hi @Anonymous，

I have made a test with the formula you provided and I got the result below.

I don't konw how do you create the measure PM cost Q1 2018 and PM cost Q2 2018.

I create the measure below.

PM Cost 2018 Final =
CALCULATE (
    VAR IsQ1Selected =
        IF (
            ISFILTERED ( 'Date'[Quarter] )
                && MAX ( 'Date'[Quarter] ) = "Qtr 1",
            TRUE,
            FALSE
        )
    VAR IsQ2Selected =
        IF (
            ISFILTERED ( 'Date'[Quarter] )
                && MAX ( 'Date'[Quarter] ) = "Qtr 2",
            TRUE,
            FALSE
        )
    VAR Calc1 =
        CALCULATE ( SUM ( Table1[Cost] ), FILTER ( 'Date', 'Date'[Quarter] = "Qtr 1" ) )
    VAR Calc2 =
        CALCULATE ( SUM ( Table1[Cost] ), FILTER ( 'Date', 'Date'[Quarter] = "Qtr 2" ) )
    RETURN
        IF (
            IsQ1Selected,
            Calc1,
            IF (
                IsQ2Selected,
                Calc2,
                IF ( IsQ1Selected && IsQ2Selected, Calc1 + Calc2, 0 )
            )
        )

Is this output above you want? If it is, you could refer to my fomula and the test pbix which has been attached below.

If you need further, please share me some data sample and your desired output.

Best Regards，

Cherry

[Anonymous]

Hi @v-piga-msft, Isn't it total should be 1200 when you have selected Q1 and Q2 both. ?  For me if both selected I need 1200 as answer.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Sorry for my misunderstanding. Try this formula again.

PM Cost 2018 Final = 
CALCULATE (
    VAR IsQ1Selected =
        IF (
            ISFILTERED ( 'Date'[Quarter] )
                && MAX ( 'Date'[Quarter] ) = "Qtr 1",
            TRUE,
            FALSE
        )
    VAR IsQ2Selected =
        IF (
            ISFILTERED ( 'Date'[Quarter] )
                && MAX ( 'Date'[Quarter] ) = "Qtr 2",
            TRUE,
            FALSE
        )
    VAR Calc1 =
        CALCULATE ( SUM ( Table1[Cost] ), FILTER ( 'Date', 'Date'[Quarter] = "Qtr 1" ) )
    VAR Calc2 =
        CALCULATE ( SUM ( Table1[Cost] ), FILTER ( 'Date', 'Date'[Quarter] = "Qtr 2" ) )
    VAR Calc3 = Calc1 + Calc2
    RETURN
        SWITCH(TRUE(),IsQ1Selected,Calc1,IsQ2Selected,Calc3,0))

Best Regards,

Cherry

[Anonymous]

@v-piga-msft I have one more question related with this. If in the near future I have to select Q3 and Q4 as well, what will be the Switch statement looks like then ?

[Anonymous]

Hi @v-piga-msft Cheery, Thanks a lot. It worked as expected. Have a nice day 🙂