topic Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows. in DAX Commands and Tips

DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

Ereon — Fri, 30 May 2025 14:02:53 GMT

I have Circana/IRI sales data coming in through a SQL connection joined to 4 identifier tables (Geographies, Product ID, Reporting Periods, and Category). The main Sales Table has three rows for Total US sales (RMA, CRMA MULO+, and CRMA MULO+ Conv). Depending on the table/filters, I need to divide a Geographies sales by the Total US sales, related to which Geography type. All Geographies have the same 3 Category break outs as Total US.

I need to calculate Share of a Geography by Reporting Period, by Product, by Category using the correct Total US Geography and match to the same RMA, CRMA - MULO+, or CRMA - MULO+ Conv.

Share = (Current Geography ID) / (Total US)

Sales Table

Reporting Period ID	Geography ID	Product ID	Category ID	$ Sales	LY $ Sales	Share
15	1	123	456	20000	19000	100%
15	2	123	456	35000	34000	100%
15	3	123	456	40000	39000	100%
15	4	123	456	5000	4500	25.0%
15	5	123	456	5500	5000	15.7%
15	6	123	456	6000	6500	15.0%
15	7	123	456	3500	3000	17.5%
15	8	123	456	4200	4400	12%
15	9	123	456	5200	5500	13%

(note that there are several different Reporting Periods, Product IDs, and Category IDs for each Geography ID)

Geography Table

Geography ID	Geography_Name	RMA_CRMA_MULTI
1	Total US - Food	RMA
2	Total US - MULO+	CRMA - MULO+
3	Total US - MULI+ Conv	CRMA - MULO+
4	Atlanta - CRMA - Food	CRMA
5	Atlanta - CRMA - MULO+	CRMA - MULO+
6	Atlanta - CRMA - MULO+ Conv	CRMA - MULO+
7	Atlanta - RMA - Food	RMA
8	Central - CRMA - Food	CRMA
9	Central - CRMA - MULO+	CRMA - MULO+
10	Central - CRMA - MULO+ Conv	CRMA - MULO+
11	Central - RMA - Food	RMA

Any help is greatly appreciated.

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

bhanu_gautam — Fri, 30 May 2025 14:31:06 GMT

Ereon First, you need to create a measure that calculates the Total US sales for each combination of Reporting Period, Product, and Category.

DAX
TotalUSSales =
CALCULATE(
SUM(SalesTable[$ Sales]),
SalesTable[Geography ID] IN {1, 2, 3}
)

Next, create a measure that calculates the sales for the current Geography ID.

GeographySales = SUM(SalesTable[$ Sales])

Finally, create a measure to calculate the share of the current Geography sales relative to the Total US sales.

DAX
Share =
DIVIDE(
[GeographySales],
[TotalUSSales],
0
)

Ensure that your report or visual applies the necessary filters for Reporting Period, Product, and Category to correctly calculate the share for each combination.

Use the Geography Table to map each Geography ID to its corresponding Total US Geography type (RMA, CRMA - MULO+, CRMA - MULO+ Conv) to ensure the correct Total US sales are used in the calculation.

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

SamsonTruong — Fri, 30 May 2025 15:20:15 GMT

Hi Ereon ,

Below is a DAX measure that can be applied to your use case:

Share = DIVIDE( SUM(Sales[$ Sales]), CALCULATE( SUM(Sales[$ Sales]), REMOVEFILTERS(Geography), TREATAS( VALUES(Geography[RMA_CRMA_MULTI]), Geography[RMA_CRMA_MULTI] ), FILTER( ALL(Geography), SEARCH("Total US", Geography[Geography_Name], 1, 0) > 0 ) ) )

Please let me know if this works as intended.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

Ereon — Fri, 30 May 2025 15:11:45 GMT

Thank you bhanu_gautam. I forgot that simple lesson I learned when making another dashboard!

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

Ereon — Fri, 30 May 2025 15:15:35 GMT

Get this error when attempting:

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

SamsonTruong — Fri, 30 May 2025 15:19:34 GMT

Hi Ereon , apologies, that was a typo on my end. Please try the following:

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

Ereon — Fri, 30 May 2025 15:22:23 GMT

Perfect, that worked! I was looking for it all in one measure just to reduce the amount of Measures I have.

Re: DAX Measure to divide each row by a Total Row within the same table. There are three total rows.

SamsonTruong — Fri, 30 May 2025 15:23:14 GMT

Ereon Awesome to hear it worked!