Weekly Sales vs Monthly Forecast in Matrix Visual

[Owen_M]

Hello,

Apologies; I've only recently started learning Power BI, I have tried searching for answers for this issue but perhaps don't know the correct terminology.

I'm trying to recreate a report from Excel in Power BI (at least as a starting point, partly to be able to cross-check numbers are matching, partly to ease the transition for the team). In this report, I have weekly sales data, compared to a monthly forecast. There is no daily data for sales, and no weekly data for forecast. 

By following some Power BI courses, I've got both elements of the above into Power BI fairly smoothly, but I cannot figure out how to combine them into one visual (to keep them alligned when detail is expanded, or if either sales or forecast contains zero).

(The change in forecast is due to updated data, not a Power BI error!)

I'm not asking for a specific solution, like fully written formulas, more general guidance; how have people resolved this kind of issue previously? I'm currently learning about DAX formulas, I'm wondering if I need to create new measures for each week of the month, as in "Week 1 Sales", "Week 2 Sales", etc. that then update depending on my month filters, and then a seperate "Month to Date" measure to sum them and compare to the forecast measure,  rather than using the current Week Commencing and subtotal, so that each column of the table is a seperate measure so that I can get the layout I need? Or am I missing an easier solution?

As a new user, I'm frustrated/concerned that something that would be quite easy in Excel is proving so difficult in Power BI, and the training courses I'm using don't cover this very specific scenario, so I'm not sure if I'm going about this the right way or about to over-engineer a workaround that isn't needed, so just wanted to check in with the more experienced users, just in case. Any advice appreicated, thanks!

[Anonymous · Accepted solution]

Hi @Owen_M ,

There's a feature you can group visuals.

Use grouping in Power BI Desktop - Power BI | Microsoft Docs

It seems unavailable if you want to combine the two visuals into one.

As a workaround, you can show your forecast value in the subtotals using ISINSCOPE function.

You could refer to the article.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Owen_M]

Would still be curious if there's a better solution, but, in case any other newbies stumble across this, please note that "over-engineer" was a gross exaggeration, and DAX formulas aren't as scary as they sound; looking forward to continuing learning more about their capabilities!