Dynamic metrics by product by week

[Ramps]

Sourcefile contains the forecasts and actuals for various metrics by product by week:-

• Product
• Week
• Forecast Sales Qty
• Actual Sales Qty
• Forecast Sales Value
• Actual Sales Value
• Forecast Packaging Costs
• Actual Packaging Cost
• Forecast Postage Costs
• Actual Postage Cost

The goal is to have a dynamic filter to select the Metric and then display the relevant forecast and actuals for the metric:-

• Qty
• Value
• Packaging
• Or Postage

The display report with

• Product
• Week
• For the selected dynamic metric:-
  • A) Forecast for week
  • B) Actual for week
  • C) Forecast for YTD (a running total for the product up to the week number)
  • D) Actual for YTD (a running total for the product up to the week number)
  • E) Forecast for YE (total for product all of year)

So the actuals can be tracked against forecast.

Here is my first attempt (this PBIX will be available until 1May18) :-

https://modusuk-my.sharepoint.com/:u:/g/personal/mike_ramplin_onepay_co_uk/EUnVerrMLp5OuHv9F7SW8-MBQ...

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))

RETURN MySum

YE Actual X =

VAR MySelection =

SELECTEDVALUE ('Metric Selection'[Metric Name], [YE Sales Qty] )

RETURN

SWITCH (

TRUE (),

MySelection = "Qty", [YE Sales Qty],

MySelection = "Value", [YE Sales Value],

[YE Sales Qty]  )

[Ramps]

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 

https://modusuk-my.sharepoint.com/:u:/g/personal/mike_ramplin_onepay_co_uk/ERPr4x14v2tOufLW2Bm5B-QBq...

Many thanks

 

[v-chuncz-msft]

@Ramps,

You may try Unpivot Columns in Query Editor and add a calculated column.

[Ramps]

Thanks Sam.

I don’t know how to do that.  Please you could anyone elaborate preferably with an easy to understand example because I am new to DAX?

Incidentally, I know how to add in modelling a dynamic measure but is there any way to add a dynamic column in modelling instead of a measure?

The following DAX logic only works on measures and not columns …

 YE Actual X =

 VAR MySelection =

 SELECTEDVALUE ('Metric Selection'[Metric Name], [YE Sales Qty] )

 RETURN

 SWITCH (

 TRUE (),

 MySelection = "Qty", [YE Sales Qty],

 MySelection = "Value", [YE Sales Value],

 [YE Sales Qty]  )