Is there an easy way to split out targets over months based on fixed percentage?

[KatrienVds]

Hi power bi community,

I'm searching for an efficiënt way to create my monthly targets in power bi. It seems like such an 'easy' question but I cant wrap my head around it. Probably making it a lot more difficult than it should be.

Currently I have everything stored in yearly targets (& types) per salesperson. For the example I narrowed it down to users only.

 

Salesperson	target 2019	target 2020	target 2021
Person 1	100.000	200.000	100.000
Person 2	200.000	150.000	100.000
Person 3	100.000	100.000	200.000
Person 4	250.000	200.000	250.000

Now I want to spread it out over 'monthly' targets. There is a 'fixed' percentage of those yearly targets that is needed per user per month. Which I also stored in a table so I can change those easily if the estimated percentages would change over time.

Month	percent
1	5%
2	10%
3	10%
4	11%

Which leads me to having to create somthing like this, I think thats the easiest way to use the data in different graphs to split out my monthly target over monthly sales.

YYYY	MM	Salesperson	month target
2019	1	Person 1	5.000
2019	1	Person 2	10.000
2019	2	Person 1	10.000
2019	2	Person 2	20.000
2020	1	Person 1	10.000
2020	1	Person 2	7.500
2021	1	Person 1	5.000
2021	1	Person 2	5.000

Is there a formula or easy way to achieve this? I feel like it shouldn't be this hard to build / calculate this table.

The 'target table' and the 'percentage' table are manually entered tables and the 'Date' table is a calculated one in power bi. Which means I can't run an SQL query with cases as an import statement. I don't want to have to manually start working out all the calculations in excel so I can copy paste that into a new table. It would also limit my abilities to change the monthly percentages in the future, making me have to recalculate everything.

I've seen something remotely similar but that goes from months to days with a crossjoin but in this case the percentage I need per month is already pre-defined and it shouldn't generally split out over all months.

Already tried making the sum per year and show those in a table next to the dates so I could split those out over the percentages per month but then I miss my sales people. Which is also required to watch it on person level not only month level.

[Anonymous · Accepted solution]

Yes, it's not that hard to do but you have to slightly change the model to suit Power BI Best Practices. Details below.

Your input table must be in this form:

The monthly percentage table can remain as is. Then here's the Monthly Targets table:

And the code that does it:

Monthly Targets = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        CROSSJOIN(
            'Yearly Targets',
            'Monthly Percentage'
        ),
        "@Target",
            var YearlyTarget = 'Yearly Targets'[Target]
            var MonthlyPercentage = 'Monthly Percentage'[Percent]
            var MonthlyTarget = YearlyTarget * MonthlyPercentage
            return
                MonthlyTarget
    ),
    "Year", [Year],
    "Month", [Month],
    "Salesperson", [Person],
    "Month Target", [@Target]
)

You should always stick to Best Practices of data modeling if you want to have an easy time working in Power BI.

[KatrienVds]

Needed a little extra fiddling due to my salespeople complexity but it worked like a charm!
Thanks.