Calculating mix with multiple criteria

[Chrisjm15]

I have a table in Power Bi Desktop.

The table has 4 columns:

1. Month
2. Channel
3. Category
4. Units

I'm trying to create a dynamic calculation to find what mix is of units of Category by Channel by month.

Essentially, each month and each channel, the Category mix should total 100%.

Then the category underneath would be say 30% of the total channel.

How do i do that?

Additional notes - I've created a "Calculations folder" as I like to keep the Power Bis neat, which I think complicates the formula?

Thanks

Chris

[rubayatyasmin · Accepted solution]

Hi, @Chrisjm15 

Create 2 calculated column. One for total unit another for mix. Sample DAX,

Total Units = CALCULATE(SUM('YourTableName'[Units]), ALLEXCEPT('YourTableName', 'YourTableName'[Month], 'YourTableName'[Channel]))

2nd calculated column

Category Mix = 'YourTableName'[Units] / 'YourTableName'[Total Units]