I can’t fathom how to do C and D. They only solutions online seem to give a running total for the whole file rather than split by product.
I have managed to solve A, B & E for Qty and Value but the solution is very long winded and will take ages to copy for Packaging, Postage and other metrics. Is there a simpler way please ?
I welcome any suggestions on how to track / compare lots of metrics by product (or other group) using dynamic DAX code that is easy to mainatain and easy to keep adding to rather, rather than lots of column specfic hard coding.
Many thanks for helping, but please explain solutions carefully because I am new to DAX and find some of the answers on the forum confusing or don’t work as required. Thank you.
YTD Actual Sales Qty = TOTALYTD (
SUM ( 'Sourcefile'[Actual Sales Qty] ),
Week[End of Week])
YE Sales Qty =
VAR MyWeek = MAX(Week[Week])
VAR MySum = CALCULATE(SUM(Sourcefile[Actual Sales Qty]),FILTER(ALLEXCEPT(Sourcefile,Sourcefile[Product]),[Week]<=MyWeek))
I have now managed to add a selection box to dynamically choose qty or value as Metrix X.
The tables then show weekly, year to date and year end totals by product for Metrix X.
This example has required modelling 4 columns and 10 measures to display just 2 of the metrix (qty and value) dynamically. Which seems too much effort, and I feel there must be a easier way.
Can you think of a simpler way to create the same kind of reports dynamically for all the metrics (qty, value, packaging and postage) and then show the weely, ytd and ye totals for the selected metrix.
My goal is to build a "tracker" that can compare forecasts against actuals weekly for multiple metrics dynamically, with as few modelling columns and measures as possible so it is dead easy to add new metrics when required.
This example will be available on OneDrive until 1May18