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,

 

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.

 

 

 

 

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

    Anonymous

     

    I got it!!

     

    I had to re-formula a couple of measures to get it to work, but SUCCESS!!!

     

    THANK YOU so much for providing the solution! It has helped me enourmously!!

     

    Best regards,

    Paul.