Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a fact table and a product dim with a hierarchy 3 levels: Productcategory --> ProductLine --> Product
Our planing team will release a monthly target for every product, i.e.
ProductCode | Product | Target | month | year |
1 | A | 234 | 1 | 2016 |
1 | A | 238 | 2 | 2016 |
1 | A | 242 | 3 | 2016 |
1 | A | 254 | 4 | 2016 |
1 | A | 289 | 5 | 2016 |
2 | B | 1022 | 1 | 2016 |
2 | B | 1048 | 2 | 2016 |
2 | B | 1122 | 3 | 2016 |
2 | B | 1248 | 4 | 2016 |
2 | B | 1342 | 5 | 2016 |
So now i need to create some reports that show comparision between actual vs target daily, i tried to import the monthly target of each product for everyday : for example, product A- i inserted a new date column :
ProductCode | Product | Target | month | year | date |
1 | A | 234 | 1 | 2016 | 1/1/2016 |
1 | A | 234 | 1 | 2016 | 1/2/2016 |
1 | A | 234 | 1 | 2016 | 1/3/2016 |
1 | A | 234 | 1 | 2016 | 1/4/2016 |
1 | A | 234 | 1 | 2016 | 1/5/2016 |
1 | A | 234 | 1 | 2016 | 1/6/2016 |
1 | A | 234 | 1 | 2016 | 1/7/2016 |
1 | A | 234 | 1 | 2016 | 1/8/2016 |
1 | A | 234 | 1 | 2016 | 1/9/2016 |
1 | A | 234 | 1 | 2016 | 1/10/2016 |
1 | A | 234 | 1 | 2016 | 1/11/2016 |
1 | A | 234 | 1 | 2016 | 1/12/2016 |
1 | A | 234 | 1 | 2016 | 1/13/2016 |
1 | A | 234 | 1 | 2016 | 1/14/2016 |
1 | A | 234 | 1 | 2016 | 1/15/2016 |
1 | A | 234 | 1 | 2016 | 1/16/2016 |
1 | A | 234 | 1 | 2016 | 1/17/2016 |
1 | A | 234 | 1 | 2016 | 1/18/2016 |
1 | A | 234 | 1 | 2016 | 1/19/2016 |
1 | A | 234 | 1 | 2016 | 1/20/2016 |
1 | A | 234 | 1 | 2016 | 1/21/2016 |
1 | A | 234 | 1 | 2016 | 1/22/2016 |
1 | A | 234 | 1 | 2016 | 1/23/2016 |
1 | A | 234 | 1 | 2016 | 1/24/2016 |
1 | A | 234 | 1 | 2016 | 1/25/2016 |
1 | A | 234 | 1 | 2016 | 1/26/2016 |
1 | A | 234 | 1 | 2016 | 1/27/2016 |
1 | A | 234 | 1 | 2016 | 1/28/2016 |
1 | A | 234 | 1 | 2016 | 1/29/2016 |
1 | A | 234 | 1 | 2016 | 1/30/2016 |
1 | A | 234 | 1 | 2016 | 1/31/2016 |
and use MAX fuction in DAX : Acctualvs.Target = SUM(Quantity)/Avg(Target). However, when i show only Product in report then the result was correct but when i show Product hierarchy with ProductCategory||ProductLine|| Product in report, the result was incorrectly because the avg function was not sum total product target for each Productline and Sum total Productline target for Product Category!.
Please give me some advice to resolve this issue, it is very important in all my report! daily actual vs monthly target.
Thanks & best regards,
J.
Solved! Go to Solution.
Hi @MichaelJackpbi,
Let me refine the fact/dim that we are having:
My simulate data in excel:
Firstly, I will calculate target for each transaction (daily target)
In Query Editor -> New Query -> Blank Query
Define GetTarget method to lookup in monthly planning table by Product Code and Month and return target value as result:
(productCode,transactionDate) => let Source = #"Monthly Planning", #"Filtered Rows" = Table.First(Table.SelectRows(Source, each ([ProductCode] = productCode and [Month] = Date.Month(transactionDate) and [Year] = Date.Year(transactionDate) )) ), #"Target" = Record.Field(#"Filtered Rows","Target") in #"Target"
In Query Editor, Choose Sales Trans, Add Column tab, Choose Invoke Custom Function to add new column by above func
Now we have Sales Trans with Target column. But there is one problem with the Sum method of Target for Product Line or Product Category that you have multiple transactions in same date. This reason will make wrong value of Target when choose higher product level in filters.
So I summarize Sales Trans table by Date and Product Code to ensure there is only one line transaction for each date.
Sales = SUMMARIZE('Sales Trans','Sales Trans'[Date],'Sales Trans'[ProductCode],"Amount",sum('Sales Trans'[Sales Amount]),"Target",AVERAGE('Sales Trans'[GetTarget]) )
Making relationships for Sales table:
I think we should add Cummulative value to current report, so I create Calculated Measure:
Cummulative = CALCULATE(sum('Sales'[Amount]),filter(ALL('Dates'),'Dates'[Date]<= MAX('Dates'[Date]) ))
That's all, now you could check by select Product line or select all product, it will show the target which was total of products target.
Sample pbix file and data:
If this works for you please accept it as solution and also give KUDOS.
Do you have sample file in there? so I could understand correctly your situation.
Its just my idea to create new reports that served for someone in planing depr only! we have a ERP system containing daily sales records and we need to compare the monthly target with actual sales to know about the percentage between them or show up the growth. Our issue was the target released monthly and transaction system exports data daily!
If u used other BI tools, i can be able to fill in avg value for each day in month depended on the monthly value.
Could you have any advice/solution to apply in power bi correctly?
regards,
J.
Hi @MichaelJackpbi,
Let me refine the fact/dim that we are having:
My simulate data in excel:
Firstly, I will calculate target for each transaction (daily target)
In Query Editor -> New Query -> Blank Query
Define GetTarget method to lookup in monthly planning table by Product Code and Month and return target value as result:
(productCode,transactionDate) => let Source = #"Monthly Planning", #"Filtered Rows" = Table.First(Table.SelectRows(Source, each ([ProductCode] = productCode and [Month] = Date.Month(transactionDate) and [Year] = Date.Year(transactionDate) )) ), #"Target" = Record.Field(#"Filtered Rows","Target") in #"Target"
In Query Editor, Choose Sales Trans, Add Column tab, Choose Invoke Custom Function to add new column by above func
Now we have Sales Trans with Target column. But there is one problem with the Sum method of Target for Product Line or Product Category that you have multiple transactions in same date. This reason will make wrong value of Target when choose higher product level in filters.
So I summarize Sales Trans table by Date and Product Code to ensure there is only one line transaction for each date.
Sales = SUMMARIZE('Sales Trans','Sales Trans'[Date],'Sales Trans'[ProductCode],"Amount",sum('Sales Trans'[Sales Amount]),"Target",AVERAGE('Sales Trans'[GetTarget]) )
Making relationships for Sales table:
I think we should add Cummulative value to current report, so I create Calculated Measure:
Cummulative = CALCULATE(sum('Sales'[Amount]),filter(ALL('Dates'),'Dates'[Date]<= MAX('Dates'[Date]) ))
That's all, now you could check by select Product line or select all product, it will show the target which was total of products target.
Sample pbix file and data:
If this works for you please accept it as solution and also give KUDOS.
I just found this post and saw your reply and i think you could help me with me problem, it is the same as this post but i need weekly targets.
Heres the link to my question:
@tringuyenminh92 thank for your help! but i am still not understand your advice totally. Why u had to create a blank query? and i see the calculation like a MTD formular.
Im sorry for missunderstanding
Regards,
J.
Hi @MichaelJackpbi,
Cause we just configure monthly target so I have to create one method(blank query) to reference to monthly target configure and get that target value for each day of month. if we do that, we don't need to manually configure daily target as your second table.
Hi @ovetteabejuela,
As my checking, files are still available - Sample pbix file and data:
@tringuyenminh92 thanks, I just realized DropBox is blocked in the office, but I got them now... Thank you.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |