Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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]))