Calculate Hierarchical Values in Matrix as a % of Total

[djkoenig]

Hello Experts, 

I am possibly making a mountain out of a molehill. 

I have a table (above) which has a few attributes. 3000732 is a product and US0XXXXX is a Cost Center. For each month, I want to see the % distribution of the cost center per product. For example, for 3000732 in September, I would want the column to display 100% for the bolded value, and then 1.11%, 2.83%, 15.21%, 27.61%, etc. as opposed to the number values. For whatever reason, I can't quite it to work. I'd really appreciate some assistance. 

A PBIX is linked here with some sample data: https://drive.google.com/file/d/1OhL_RLuvsy0C6b9djgl4Lgf69EN4-n16/view?usp=sharing 

Let me know if you need anything further or have any questions!

[djkoenig]

I really appreciate the effort in your post @AnalyticsWizard. Although, I do think Pivoting the columns and creating a seperate table would have worked, I ended up just changing your code slightly, and it seems to be both dynamic and producing the correct % breakdown. 

My Measure:

% Allocation = 'Edw V_GoodsMovementDocument'[Total Consumption]/Calculate(Sum('Edw V_GoodsMovementDocument'[Total Consumption]),ALLEXCEPT('Edw V_GoodsMovementDocument','Edw V_GoodsMovementDocument'[Material_NZ],'Edw V_GoodsMovementDocument'[FiscalYearPeriod]))

 Where I defined Total Consumption from Quantity in Base Unit based on Goods Movement Type in Power Query,  (i.e. types indicate whether it is a consumption posting and the +/- for that consumption value). This way it was availiable as a calculated column. 

Looking back, I really did make this much harder than it needed to be! Thank you for the assist! 

[AnalyticsWizard · Accepted solution]

@djkoenig 

1. Create a Measure for Monthly Percentage: First, you’ll need to create a measure that calculates the percentage of the total for each row. Let’s call this measure "Monthly Cost Center %".

Here's what the DAX formula might look like:

Monthly Cost Center % =
DIVIDE(
SELECTEDVALUE('Table'[Current Month Value]),
CALCULATE(
SUM('Table'[Current Month Value]),
ALLEXCEPT('Table', 'Table'[Material_NZ], 'Table'[Month Name])
),
0
)

In this formula, replace `'Table'` with your actual table name, `'Current Month Value'` with the value for the current month you are looking at (e.g., 'Table'[September]), and `'Month Name'` with the actual month's column you're evaluating.

Explanation:
- `SELECTEDVALUE('Table'[Current Month Value])` gets the value for the current row.
- The `CALCULATE(SUM(...), ALLEXCEPT(...))` part sums up all values for the product in the current month, ignoring filters that might be applied to other columns except the product (`Material_NZ`) and the month you're evaluating.
- `DIVIDE` function is used to perform the division and handle division by zero by returning 0 when necessary.

2. Apply the Measure to the Table Visual: Add this measure to your table visual. To do this:
- Drag the new measure into the Values area of the table visual.
- Make sure to have the 'Material_NZ' and 'Month Name' columns in the table as well, so that Power BI knows over which fields to calculate the percentages.

3. Format the Measure as Percentage: To display the result as a percentage:
- Go to the Modeling tab.
- Select the "Monthly Cost Center %" measure.
- In the Formatting section, choose the percentage format and specify the desired number of decimal places.

When you add this measure to the table visual alongside the month columns, it should display the percentage distribution of the cost center per product for each month.

If you want this calculation for all months without creating a separate measure for each one, you would need to pivot your month columns so that you have a single "Month Value" column and a "Month" column that specifies which month the value pertains to. Then you can write a more generalized measure that works across all months.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[v-tianyich-msft]

Hi @djkoenig ,

It can only maintain the same format, so you may need to create two tables to accomplish this.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[djkoenig]

Honestly, did that help, not really. I do appreciate you taking a look though! Is it possible that you could explain (or create) this second table from the sample data and how it would join back to the main table. I've tried a few things on my end and I haven't quite figured out the pieces of the puzzle. 

For the two table approach, I have created a summary table: 

Consumption by Cost Center = ADDCOLUMNS( SUMMARIZE('Edw V_GoodsMovementDocument','Edw V_GoodsMovementDocument'[CostCenter],'Date'[Year Month]),     "Subtotal", CALCULATE(('Edw V_GoodsMovementDocument'[Total Consumption]),ALLEXCEPT('Edw V_GoodsMovementDocument','Edw V_GoodsMovementDocument'[CostCenter],'Date'[Year Month]))

and then converted to Power Query so I could join the summary table with the main table. 

This will cause the cost center summarized piece to get duplicated, but I figured I could just divide by the count of the Cost Center to get rid of this issue. This does not work universally for all the cost centers though.... 

Which I don't think this is even the right approach. Am I right to assume, I basically need a calendar table with year month, calculate total consumption product level for year month, then calculate cost center level production for each year month and then join the values based on the year month dimension?

Also, since I'm stubborn, I went back to the one table approach (cause I swear you can do it this way). 

I am using a measure like this: 

TotalProductConsumption = 
CALCULATE(
    [Total Consumption],
    ALLEXCEPT('Edw V_GoodsMovementDocument', 'Date'[Year Month], 'Edw V_GoodsMovementDocument'[Material_NZ])
)

And then saying: 

PercentageOfTotal = 
DIVIDE(
    [Total Consumption],
    [TotalProductConsumption]
)*100

This gets me the right breakdown of % of reagents/materials per cost center. However, the total does not show as 100% per product and you cannot drill down into multiple materials. 

This caused me to stumble across this Hierarchies – DAX Patterns. 

Which makes me feel like you can get to a one table solution, using some combination of HASONEFILTER, ISINSCOPE, and the SWITCH formula. However, I haven't gotten it all to work out. 

I'd appreciate any guidance at this point, doesn't even have to be good 🙂