How to Calculate Share Measures using Share Bases Identified in a Reference Table?

[davebourgeois87]

Hi Everyone,

I'm looking for guidance about how to incorporate a product reference table so that I can calculate multiple product share bases (i.e, category, segment, department, etc.). I have a normalized dataset that uses ProductID as the key between my data table and my product reference table and the relationship is set up correctly (simplifed example below). 

What I would like to be able to do is set up calculations like this: 

Dollar Share Segment = 
Var Numerator = [Dollars]
Var Denominator = Calculate([Dollars],ProductID = SegmentID

Return Numerator/Denominator

Dollar Share Category = 

Var Numerator = [Dollars]

Var Denominator = calculate([Dollars],ProductID = CategoryID

Return Numerator/Denominator

What I'm struggling with is the DAX function required to calculate the denominator. I'm not sure how to pull in the dollars for the required ProductIDs. I am not able to share an actual file due to data privacy guidelines that my company and my client have, but I'm just looking for any suggestions on which dax functions to use. Any help is greatly appreciated!

[v-henryk-mstf · Accepted solution]

Hi @davebourgeois87 ,

To calculate share measures using share bases identified in a reference table, you can use a combination of the SUMPRODUCT and DIVIDE functions in a calculated column in your data table.

For example, if you have a table of sales data with columns for ProductID, Sales, and Category, and a reference table with columns for ProductID and Category, you can create a calculated column in your sales table called "Category Share" using the following formula

Category Share = SUMPRODUCT(Sales, DIVIDE(ProductID, Category))

This formula will use the reference table to identify the category for each product, and then calculate the share of sales for that category by summing the sales for each product and dividing by the total sales for the category.

You can use this same approach to calculate share measures for other share bases, such as segment, department, or any other category that you have identified in your reference table. The key is to use the reference table to identify the share base for each product, and then use the SUMPRODUCT and DIVIDE functions to calculate the share of sales for that share base.

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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