Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have 2 tables ( one has all the sales info) and the other has de budget allocation for each salesperson by year and month for the last 3 years ( 2017, 2018 and 2019)
How can I calculate the budget for each sales person for each year and month?
I have to allocate by salesperson, by year and by month, but treatas only let's me allocate once.
How could I create a measure for total budget that works for this?
Solved! Go to Solution.
Hi @Anonymous ,
Change the data type to whole number in data pane as the picture below.
Regards,
Frank
Hi @Anonymous,
Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.
Regards,
Frank
Hi @Anonymous,
1. To create a calculated column in BUDGET table.
YearMonth = BUDGET[YEAR]*10000+'BUDGET'[MONTH]*100
2. Create a measure as below.
TOTAL BUDGET1 = CALCULATE(SUM(BUDGET[OBJECTIVE]), TREATAS(VALUES('DATES'[MonthnYear]),'BUDGET'[YearMonth]))
Regards,
Frank
Hi @Anonymous ,
Change the data type to whole number in data pane as the picture below.
Regards,
Frank
Hi Frank v-frfei-msft !
Because the month value is text, I can't multiply by 100.
Is there any way else?
Thanks
Telmo
Hi!
Because the month value is text, I can't multiply by 100.
Is there any way else?
Thanks
Telmo
TOTAL BUDGET doesn't iterates by month, so the measure total budget = cumulative budget.
I have sent u the excel file and PBI file.
And budget sucess is exactly the same for each month wich is wrong.
Thank u!
https://1drv.ms/f/s!AuSuG7Ypxe7xiUPp2MM28cFgJtfj
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |