Forum Discussion

Chrisjm15's avatar
Chrisjm15
Regular Visitor
2 years ago
Solved

Calculating mix with multiple criteria

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

 

Tips and Tricks
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    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]

     

1 Reply

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    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]