Average Over Time by the Department of the selected Dimensions

krishgk_ispot — Thu, 08 Nov 2018 02:33:05 GMT

HI,

I would like to create a line chart which has

Year-Month as dimension and

measures are Profit Ratio (of Current Selection) and

Average Profit Ratio for the Department of the selected Product at that point in time (for each of the Month).

When a User selects a Product/Employee the chart should display the Profit Ratio over time along with the average Profit Ratio of the Department over-time so it will be easy to compare data in the same chart.

Profit Ratio = Sum(Income) / Sum(Sales)

The Department average shows the Overall as a single dot instead of showing a line chart Over Time.

Average Profit Ratio for the Department Formula:

Sum(Aggr(Sum({$<[$(=Concat({1<$Field-={'Date','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Income]),[Department]))

Sum(Aggr(Sum({$<[$(=Concat({1<$Field-={'Dat','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Sales]),[Department]))

Can you please help me to get the industry average over time as separate line..

Re: Average Over Time by the Department of the selected Dimensions

sunny_talwar — Thu, 08 Nov 2018 11:29:57 GMT

Try this may be and see if this gives you what you want

Sum(Aggr(Sum(TOTAL <[Year-Month]> {$<[$(=Concat({1<$Field-={'Date','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Income])/Sum(TOTAL <[Year-Month]> {$<[$(=Concat({1<$Field-={'Dat','Year','Year Month','Year Quarter'}>}distinct $Field,']=,[')&']=')>} [Sales]), [Department], [Year-Month]))

topic Average Over Time by the Department of the selected Dimensions in App Development

Average Over Time by the Department of the selected Dimensions

Re: Average Over Time by the Department of the selected Dimensions