cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Divide filtered value by sum of month (same column)

Hi,

I have a dashboard with a few slicers (date, segment, etc).

I need help to create a measure that divides the outcome of slicers by the sum of the month.
For example, when I choose a date and segment the outcome of sales is 2.000, and the total sales of the selected month are 10.000 then the result has to be 2.000 (with slicer selections) / 10.000 (with a selected month from slicer) = 20%.

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

Try to create measures as follows:

``_sum = SUM('Table'[Sale])``
``_sumOfMonth = CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))``
``_result = DIVIDE([_sum],[_sumOfMonth])``

result:

In addition, I have to say that the measure mentioned by @VahidDM  above can also work and only one measure is used.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

4 REPLIES 4
Anonymous
Not applicable

Thank you all for your replies! I ll try to make it more clear!!

I m trying to create the third visual (percentage card) on an easy way.

Selected sales and percentage are dynamic (I prevent Total Sales from changing, because its a stable number).

Super User

Hi,

You should have a Calendar Table with a Year, Month name and Month number column.  Sort the Month number by the Month name column.  Create a relationship from the Date column of your base data table to the Date column of the Calendar Table.  Create 2 slicers for Year and Month name.  Select a Year and Month name.  Write these measures:

Total sales = SUM(Data[Sales])

Total sales in month = calculate([Total sales],all(data[segment]))

Ratio = divide([Total sales],[Total sales in month])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi, @Anonymous

Try to create measures as follows:

``_sum = SUM('Table'[Sale])``
``_sumOfMonth = CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))``
``_result = DIVIDE([_sum],[_sumOfMonth])``

result:

In addition, I have to say that the measure mentioned by @VahidDM  above can also work and only one measure is used.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

Community Champion

Hi @Anonymous

Try to create a measure with your data like this:

Measure =
Var _SelectedDate =SELECTEDVALUE('Table'[Date])
Var _MonthSD = month(_SelectedDate)

Var _DateAmount = calculate(max('Table'[amount column],'Table'[Date]=_SelectedDate)
Var _MonthAmount = calculate(sum('Table'[amount column],filter(all('Table'),month('Table'[Date])=_MonthSD
return
_DateAmount/_MonthAmount

Change the format of this measure to percentage.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!