Creating a measure to divide a column by a measure

Anonymous — Tue, 04 May 2021 19:55:04 GMT

Hi All,

I have created a measure that allows me to see the total sales for all stores that have a rate that is not blank. This report will be focused on a region, so a slicer will decide which region will be showing. Now, I would like to divide the stores that are not blank's total sales by the total for the region. This will leave me with a percent of the of the sales that are reporting rates for each store that is reporting a rate.

Here's my first measure to get the total sales of stores that do not have a blank rate:

Weighted Total = (CALCULATE(SUM('Main'[Total Sales]),FILTER(Main,VALUE('Main'[Total Rate])<> BLANK())))

Store	Total Sales	Total Rate	Region
A	100	.02	1
B	150	.04	1
C	125	.02	1
D	50	-	1
E	100	-	1
F	200	.07	2
G	110	.01	2
H	60	-	2
I	75	.05	2

In the sample data, my first measure would exclude stores D & E for Region 1, and the total sales would be 375. I would then want to add a column to this table that would divide the store's total sales (if they reported a rate) by the regional total. For example, it would do the 100/375 calculation for Store A and leave me with 27%. in another column in the table.

Thanks in advance

Re: Creating a measure to divide a column by a measure

AlexisOlson — Tue, 04 May 2021 20:34:01 GMT

Just to be clear, you cannot use slicer selections in a calculated column. Calculated columns and calculated tables cannot be responsive to slicers since they are only computed once per data load.

Re: Creating a measure to divide a column by a measure

Anonymous — Tue, 04 May 2021 20:55:50 GMT

I think I misspoke a bit in the last paragraph. I'm not looking for a calculated column, but rather using a table visual to display this data. I would want to add that measure as a field in my table visual.

topic Creating a measure to divide a column by a measure in DAX Commands and Tips

Creating a measure to divide a column by a measure

Re: Creating a measure to divide a column by a measure

Re: Creating a measure to divide a column by a measure