## Desktop

Powerbiuser206 Regular Visitor
Posts: 30
Registered: ‎05-25-2018 # Calculate Month Total with selected Filter date

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.

Thank You in Advance

Power BI User

Accepted Solutions Community Support Team
Posts: 1,613
Registered: ‎07-30-2018 ## Re: Calculate Month Total with selected Filter date

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

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

All Replies
Highlighted
Anonymous Posts: 0

## Re: Calculate Month Total with selected Filter date

Hello,

See image I've attached and tell us if works for you.  Community Support Team
Posts: 1,613
Registered: ‎07-30-2018 ## Re: Calculate Month Total with selected Filter date

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Powerbiuser206 Regular Visitor
Posts: 30
Registered: ‎05-25-2018

## Re: Calculate Month Total with selected Filter date

Thanks a lot Lin. This Solution worked for me.