Adding year to the formula calculation

[Anonymous]

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.

[v-shex-msft · Accepted solution]

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

[v-shex-msft · Accepted solution]

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]

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

[v-shex-msft · Accepted solution]

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

[Anonymous]

Thank you @v-shex-msft 🙂

[Anonymous]

@v-shex-msft

[Anonymous]

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.

[Ashish_Mathur]

Hi,

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