I have a table that identifies the top 6 suppliers for a specific product type based on sales. The table also includes measures to calculate Gross Profit and Sales Mix %. I have a separate dimension (Monthly Margin Dim) which splits these values by previous month (Oct 2022), 2022 YTD and 2021 YTD.
The Sales Mix % formula (see below) works perfectly when I make a selection i.e. 2022 YTD. When I remove my Monhtly Margin Dim selections the calculation stops working.
Sales Mix % formula:
Sum({<[Product Forecast Category]={'GENERATORS'},[Product Group Description]={'Boilers'}>} [Sales Invoice Line Amount])
/
Sum(TOTAL[Sales Invoice Line Amount])
I have tried including <[Monthly Margin Dim]> in the denominator and numerator but this doesn't work either.
Any suggestions?
