Forum Discussion

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
9 years ago
Solved

Combining Past Sales and Projected Sales

Good Morning everyone,   I would really appreciate some guidance as to how to resolve my current issue. I have a model including sales and projected sales tables, with a period table relating both ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi PaulDBrown,

     

    You can try to use below measure to deal with the total row calculate:

     

    Total Measure(Row Count) = 
if(COUNT('Table'[Date])=COUNTX(ALL('Table'),[Date]),
 "All", 
 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
	 "Year Level Total",
	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])),[Date]),
		 "Quarter Level Total",
		 	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
		 	"Month Level Total","Day Level Total"
		 ))))

     

     

    You only need to input your formula at the specify "total level" to config the calculation on total level.

     

    Notice: use your columns to replace the different hierarchy level column name.

     

    If above not help, you feel free to post here.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi PaulDBrown,

 

Can you share a sample file to test or provide more detail content? I'm not so sure for your measures which used in the formula.

 

Regards,

Xiaoxin Sheng

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Hi Anonymous

     

    Thank you for taking time to help me with this! I am at present stuck with the project I'm working on becasue of this problem.

    Here is the link:

     

    Sales and projection example

     

    The sales values need to be  upto and including March; the projected values need to be  from April onwards.

     

    Basically what I'm trying to do is create a new "running total" combining past sales with future "projected sales or forecasts/estimates". The idea is that a user can understand how these estimates affect the overall performance of the company looking into the future  (even if it is only the coming month).

     

    As you can see in the table in the example, the values in for the individual months seem to be working: I get sales figures for Months Jan-March, and projected values from April onwards.

     

    However the subtotals do not match; the values displayed are always the subtotals for projected, when what I need (in order to be able to summarize by quarter for example, or create groups by quarter in a chart with filter down options) is that these subtotals also respect the timeframe condition: Subtotal Jan-March is the sales value; subtotal form April onwards is the projected value.

     

    Thnak you again for your time.

     

    Best regards,

     

    Paul.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi PaulDBrown,

       

      You can try to use below measure to deal with the total row calculate:

       

      Total Measure(Row Count) = 
if(COUNT('Table'[Date])=COUNTX(ALL('Table'),[Date]),
 "All", 
 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
	 "Year Level Total",
	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])),[Date]),
		 "Quarter Level Total",
		 	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
		 	"Month Level Total","Day Level Total"
		 ))))

       

       

      You only need to input your formula at the specify "total level" to config the calculation on total level.

       

      Notice: use your columns to replace the different hierarchy level column name.

       

      If above not help, you feel free to post here.

       

      Regards,

      Xiaoxin Sheng

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Anonymous

         

        Thank you so much for taking the time and helping me out. Unfortunately it doesn't seem to work. I have had to adapt the measure to account for the fact that "Projected sales" has a monthly granularity and therefore I need to use the YearMonth period table as a time reference. (Projected Sales table doesn't have individual dates).

         

        The result is what you can see in the "TESTING Total Measure (Row Count) column.

         

        And the sub-totals displayed still display only the values from the projected sales.

         

        Here is the file with the new measure applied:

        Current and Projected Sales File Sample

         

        What am I doing wrong?

         

        Thank you again for your help.

         

        Best regards,

        Paul.