Forum Discussion

NickProp28's avatar
NickProp28
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

Count and sum for specific column then compare between years

Dear experts,

 

Good day. I new to Power BI, and would like get some help from you guys.

In short,here is my data.

 

Date                Month    Country   Department

9/6/2019JuneChennaiIT
9/6/2019JuneChennaiIT
9/6/2019JuneUSAIT
9/6/2020JuneUSAHR
10/6/2020JuneUSAHR
10/7/2019JulyChennaiIT
11/7/2019JulyChennaiHR
11/7/2020JulyUSAHR
11/7/2020JulyUSAIT

 

And here my expected outcome .Count and sum the department, and i would like to compare between two years. (Will add country in my slicer, but if not the outcome will like this)

 20192020
June32
July22


My sincerely appreciate for the help.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi NickProp28 ,

     

    I think it would be very strange for you to add measure 2 to the same matrix, because the column value of your matrix are years.

    So I created another table visual.

     

    Are you sure what you want is the following visual effect?

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi NickProp28 ,

     

    Please use the formula.

    Measure 2 = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
       ALLSELECTED( Sheet4),
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2019
    )
)
VAR y = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        ALLSELECTED(Sheet4),
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2020
    )
)
RETURN
DIVIDE(
    y, x, BLANK()
)

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

    • NickProp28's avatar
      NickProp28
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Dear v-lionel-msft ,

      Based on this question, can you help me out on divide this two years (Year 2020/year 2019)? I want get the Variance.
      Thanks so much if you could help. 

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi NickProp28 ,

         

        You can do like this.

         

        Measure 2 = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        Sheet4,
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2019
    )
)
VAR y = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        Sheet4,
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2020
    )
)
RETURN
DIVIDE(
    y, x, BLANK()
)

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.