Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help with a measure

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.

Need Help
Show and Tell
Tips and Tricks
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

5 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        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