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

[Ereon]

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.

[bhanu_gautam · Accepted solution]

@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.

[SamsonTruong]

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

[Ereon]

Get this error when attempting:

[SamsonTruong · Accepted solution]

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

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
        )
    )
)

[Ereon]

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

[SamsonTruong]

@Ereon Awesome to hear it worked!

[bhanu_gautam · Accepted solution]

@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.

[Ereon]

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