Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Adding year to the formula calculation

Hi, 

 

I have a formula which is mentioned below:

 

Total Number of Class A = CALCULATE(COUNTROWS('SHOP Final Assy-Casework'), FILTER(ALL('SHOP Final Assy-Casework'), 'SHOP Final Assy-Casework'[Class Level] = "Class A"),FILTER(ALL('SHOP Final Assy-Casework'),'SHOP Final Assy-Casework'[Month]=MAX('SHOP Final Assy-Casework'[Month])))

 

but, however, it doesn't add the count year-wise. In other, it is only taking 2017 values not the 2018 one's. Please let me know what should I add to calculate per year-wise.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    How about below formula, you can try it if it works for your scenario:

    Total Number of Class A =
CALCULATE (
    COUNTROWS ( 'SHOP Final Assy-Casework' ),
    FILTER (
        ALLSELECTED ( 'SHOP Final Assy-Casework' ),
        'SHOP Final Assy-Casework'[Class Level] = "Class A"
    ),
    VALUES ( 'SHOP Final Assy-Casework'[Year] ),
    VALUES ( 'SHOP Final Assy-Casework'[Month] )
)

    If not help, please share some sample data for test and coding formula.

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    If you mean you wanted to calculate cumulative total, you can take a look at following blog:

    Cumulative Total

     

    In addition, you can remove 'class level' filter condition to get total count of specific records:

    Total Number of Class A =
CALCULATE (
    COUNTROWS ( 'SHOP Final Assy-Casework' ),
        ALLSELECTED ( 'SHOP Final Assy-Casework' ),
    VALUES ( 'SHOP Final Assy-Casework'[Year] ),
    VALUES ( 'SHOP Final Assy-Casework'[Month] )
)

     

    Regards,

    Xiaoxin Sheng

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous,

     

    How about below formula, you can try it if it works for your scenario:

    Total Number of Class A =
CALCULATE (
    COUNTROWS ( 'SHOP Final Assy-Casework' ),
    FILTER (
        ALLSELECTED ( 'SHOP Final Assy-Casework' ),
        'SHOP Final Assy-Casework'[Class Level] = "Class A"
    ),
    VALUES ( 'SHOP Final Assy-Casework'[Year] ),
    VALUES ( 'SHOP Final Assy-Casework'[Month] )
)

    If not help, please share some sample data for test and coding formula.

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Yes, thank you. It worked. It would be helpful if you could suggest me where can I learn DAX language. I mean for power BI. Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your help!. Alos, I wanted to know how can I calculate total submissions month wise and filtering it by year for the same sort of count which I asked before. Thank you. It would be helpful

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Share some data, describe the business question and show the expected result.