Reply
Regular Visitor
Posts: 28
Registered: ‎05-25-2018
Accepted Solution

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.
 
Please Help.
 
Thank You in Advance
 
Power BI User

 

 

 


Accepted Solutions
Community Support Team
Posts: 1,149
Registered: ‎07-30-2018

Re: Calculate Month Total with selected Filter date

hi, @Powerbiuser206

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:

4.JPG

 

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.

View solution in original post

Attachment

All Replies
Highlighted
Member
Posts: 51
Registered: ‎05-30-2016

Re: Calculate Month Total with selected Filter date

Hello,

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

 

CalculateMonthDaySelected.JPG

Community Support Team
Posts: 1,149
Registered: ‎07-30-2018

Re: Calculate Month Total with selected Filter date

hi, @Powerbiuser206

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:

4.JPG

 

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.
Attachment
Regular Visitor
Posts: 28
Registered: ‎05-25-2018

Re: Calculate Month Total with selected Filter date

Thanks a lot Lin. Smiley Happy

 

This Solution worked for me.