Reply
Frequent Visitor
Posts: 9
Registered: ‎12-07-2018
Accepted Solution

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.


Accepted Solutions
Highlighted
Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Adding year to the formula calculation

Hi @Gaurav01,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Adding year to the formula calculation

Hi @Gaurav01,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post


All Replies
Super User
Posts: 4,103
Registered: ‎01-14-2017

Re: Adding year to the formula calculation

Hi,

 

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

Highlighted
Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Adding year to the formula calculation

Hi @Gaurav01,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Frequent Visitor
Posts: 9
Registered: ‎12-07-2018

Re: Adding year to the formula calculation

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.

Frequent Visitor
Posts: 9
Registered: ‎12-07-2018

Re: Adding year to the formula calculation

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

Frequent Visitor
Posts: 9
Registered: ‎12-07-2018

Re: Adding year to the formula calculation

Community Support Team
Posts: 7,711
Registered: ‎08-14-2016

Re: Adding year to the formula calculation

Hi @Gaurav01,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Frequent Visitor
Posts: 9
Registered: ‎12-07-2018

Re: Adding year to the formula calculation

Thank you @v-shex-msft Smiley Happy