DAX Formula: Distribute Sales from one Product Line into the other Product lines based on YTD %

[dylankaiser4]

Hello, 

I have a request to reallocate values from one Product Line into the other Product Lines based on the YTD Revenue % split of the Product Lines. 

Example I pulled together in excel to visually represent this:

I am wondering how I should build out this use case. Is DAX the right approach or should a pre-process within PowerQuery?

Any assitance is greatly appreciated!

[DataNinja777 · Accepted solution]

Hi @dylankaiser4 ,

You can use dax to produce your required output, and it is simple to do so like in Excel.  One reservation regarding the YTD Revenue allocation is that, you have to be careful when you want to see the allocated sales by month granularity.  In my opinion, it appears simpler to do so by month by month distribution and add across months for YTD rather than having to subtract from current month YTD from previous month YTD to get the current month monthly revenue.  However, for this example, I did as you requested, and I attach an example pbix file below.