topic Re: How to show value from Max week selected in the filter as total column? in DAX Commands and Tips

How to show value from Max week selected in the filter as total column?

JIN23 — Thu, 01 Aug 2024 07:48:08 GMT

I have calculated sell-through and when I select multiple weeks in the filter, I want to show only sell-through of max week selected as total not sum of everything to calculate..

How can I do it?

What I want is to bring 77.2% as total ST% instead of 5.6%..

Same for the stock gross, I want to bring 503,239,352 instead of sum of everything.

I need help!

Thanks

Re: How to show value from Max week selected in the filter as total column?

aduguid — Thu, 01 Aug 2024 08:30:26 GMT

Can you please post the text for the ST% measure?

Re: How to show value from Max week selected in the filter as total column?

JIN23 — Thu, 01 Aug 2024 08:42:59 GMT

st = sales gross acc / (sales gross acc+stock gross)

Re: How to show value from Max week selected in the filter as total column?

aduguid — Thu, 01 Aug 2024 09:22:31 GMT

Try this measure. This assumes you have a calendar type table setup for the week numbers

ST = VAR MaxWeek = MAX('YourWeekTable'[WeekColumn]) -- Adjust 'YourWeekTable' and 'WeekColumn' to your table and column names VAR MaxSalesGrossAcc = CALCULATE( SUM('YourTable'[SalesGrossAcc]), 'YourWeekTable'[WeekColumn] = MaxWeek ) VAR MaxStockGrossAcc = CALCULATE( SUM('YourTable'[StockGrossAcc]), 'YourWeekTable'[WeekColumn] = MaxWeek ) VAR SalesGrossAcc = SUM('YourTable'[SalesGrossAcc]) VAR StockGrossAcc = SUM('YourTable'[StockGrossAcc]), VAR MaxSellThroughPercentage = DIVIDE(MaxSalesGrossAcc, MaxSalesGrossAcc + StockGrossAcc) VAR SellThroughPercentage = DIVIDE(SalesGrossAcc, SalesGrossAcc + StockGrossAcc) RETURN IF( HASONEVALUE('YourWeekTable'[WeekColumn]), SellThroughPercentage, MaxSellThroughPercentage )