Forum Discussion
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 -
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
3 Replies
v-lili6-msftCommunity Support
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