Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors