Calculate Month Total with selected Filter date

[Anonymous]

Hi

I have a dataset as below :

Month    Day of Month    Values   Distinct count of days      Total(Values)      Average= Total(Values)/Distinct count of days

1             1                           20                    2                                 50                             50/2

1             2                           30                    2                                 50                             50/2

2             1                           30                    2                                 70                             70/2

2             2                           40                    2                                 70                             70/2

I want to calculate average for every month on basis of filter selection . For example :

If user selects 1 and 2 Day of Month from Month-1 and 1 and 2 Day of Month from Month-2 . The above total should calculate as shown.

I am able to achieve distinct count of days with this formula -

Distinct count of days = CALCULATE(COUNTROWS(FILTERS(Table[DAY_OF_MONTH])),ALLSELECTED(Table))

I am unable to calculate Total (Values) Monthwise.

Total (Values)= CALCULATE(SUM([Values]),ALLSELECTED(Table))  - This calculates the sum for all four selected entries , i want it to sum it monthwise.

Please Help.

Thank You in Advance

Power BI User

[v-lili6-msft · Accepted solution]

hi, @Anonymous

You could try these three formulas as below:

Distinct count of days = CALCULATE(DISTINCTCOUNT('Table'[DAY_OF_MONTH]),ALLSELECTED('Table'[DAY_OF_MONTH]))


Total(Values) = CALCULATE(SUM('Table'[Values]),ALLSELECTED('Table'[DAY_OF_MONTH]))


Average = [Total(Values)]/[Distinct count of days]

Result:

and here is pbix, please try it.

Best Regards,

Lin

[Anonymous]

Thanks a lot Lin. 🙂

This Solution worked for me.

[Anonymous]

Hello,

See image I've attached and tell us if works for you.