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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Allocate monthly target to day

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.

 

ProductCodeProductTargetmonthyear
1A23412016
1A23822016
1A24232016
1A25442016
1A28952016
2B102212016
2B104822016
2B112232016
2B124842016
2B134252016

 

 

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 :

ProductCodeProductTargetmonthyeardate
1A234120161/1/2016
1A234120161/2/2016
1A234120161/3/2016
1A234120161/4/2016
1A234120161/5/2016
1A234120161/6/2016
1A234120161/7/2016
1A234120161/8/2016
1A234120161/9/2016
1A234120161/10/2016
1A234120161/11/2016
1A234120161/12/2016
1A234120161/13/2016
1A234120161/14/2016
1A234120161/15/2016
1A234120161/16/2016
1A234120161/17/2016
1A234120161/18/2016
1A234120161/19/2016
1A234120161/20/2016
1A234120161/21/2016
1A234120161/22/2016
1A234120161/23/2016
1A234120161/24/2016
1A234120161/25/2016
1A234120161/26/2016
1A234120161/27/2016
1A234120161/28/2016
1A234120161/29/2016
1A234120161/30/2016
1A234120161/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.

1 ACCEPTED SOLUTION

Hi @MichaelJackpbi,

 

Let me refine the fact/dim that we are having:

  • Sales Trans (Date, ProductCode, Sales Amount)
  • Products (Product, ProductCode, ProductLine, ...)
  • Dates (Date) (Will be created by New Table: Dates = CALENDARAUTO() )
  • Monthly Planning (Month, Year, ProductCode, Target)
  • For Daily planning, I suggest we will use Power Query funct to create this table from Monthly Planning table instead of manually input as you are doing. 

 

My simulate data in excel:

Screenshot 2016-12-04 21.33.50.pngScreenshot 2016-12-04 21.33.46.pngScreenshot 2016-12-04 21.34.16.png

 

Firstly, I will calculate target for each transaction (daily target)

In Query Editor -> New Query -> Blank Query  Screenshot 2016-12-04 21.38.04.png

Define GetTarget method to lookup in monthly planning table by Product Code and Month and return target value as result:

Screenshot 2016-12-04 21.37.51.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(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

Screenshot 2016-12-04 21.45.44.png

 

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:Screenshot 2016-12-04 21.52.11.png

 

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])  ))

 

 

Screenshot 2016-12-04 21.54.54.png

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.

 

 

View solution in original post

9 REPLIES 9
tringuyenminh92
Memorable Member
Memorable Member

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:

  • Sales Trans (Date, ProductCode, Sales Amount)
  • Products (Product, ProductCode, ProductLine, ...)
  • Dates (Date) (Will be created by New Table: Dates = CALENDARAUTO() )
  • Monthly Planning (Month, Year, ProductCode, Target)
  • For Daily planning, I suggest we will use Power Query funct to create this table from Monthly Planning table instead of manually input as you are doing. 

 

My simulate data in excel:

Screenshot 2016-12-04 21.33.50.pngScreenshot 2016-12-04 21.33.46.pngScreenshot 2016-12-04 21.34.16.png

 

Firstly, I will calculate target for each transaction (daily target)

In Query Editor -> New Query -> Blank Query  Screenshot 2016-12-04 21.38.04.png

Define GetTarget method to lookup in monthly planning table by Product Code and Month and return target value as result:

Screenshot 2016-12-04 21.37.51.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(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

Screenshot 2016-12-04 21.45.44.png

 

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:Screenshot 2016-12-04 21.52.11.png

 

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])  ))

 

 

Screenshot 2016-12-04 21.54.54.png

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.

 

 

HI @tringuyenminh92 

 

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:

https://community.powerbi.com/t5/Desktop/Power-Bi-DAX-Setting-a-Target-for-each-Week-of-Month/td-p/9...

 

 

@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.

 

This is interesting, do you still have the shared files @tringuyenminh92?

@tringuyenminh92 thanks, I just realized DropBox is blocked in the office, but I got them now... Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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