cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KatrienVds
Frequent Visitor

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

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.

 
Salespersontarget 2019target 2020  target 2021
Person 1100.000200.000100.000
Person 2200.000150.000100.000
Person 3100.000100.000200.000
Person 4250.000200.000250.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
15%
210%
310%
411%

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.

YYYYMMSalespersonmonth target
20191Person 15.000
20191Person 210.000
20192Person 110.000
20192Person 220.000
20201Person 110.000
20201Person 27.500
20211Person 15.000
20211Person 25.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.

 

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

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

 

daxer_0-1623755823209.png

Your input table must be in this form:

daxer_1-1623755880552.png

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

daxer_2-1623755940548.png

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.

 

View solution in original post

2 REPLIES 2
daxer
Solution Sage
Solution Sage

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

 

daxer_0-1623755823209.png

Your input table must be in this form:

daxer_1-1623755880552.png

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

daxer_2-1623755940548.png

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.

 

View solution in original post

KatrienVds
Frequent Visitor

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.