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
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.
Solved! Go to 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.
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.
Needed a little extra fiddling due to my salespeople complexity but it worked like a charm!
Thanks.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |