7 Replies Latest reply on Oct 5, 2018 11:55 AM by Deepak Rai

# Calculating between 2 LODs

Not sure if anyone has a solution to this or if there is a better way to it.... i am trying to calculate the values in the red square by the number in the purple square.

ie

1165 /1910

1004 / 1910

801 / 1910

as a separate calculated measure.

i am comparing the segments in 2018 by 2017's whole year average.

Appreciate any advise to the approach to take.

• ###### 1. Re: Calculating between 2 LODs

Hi,

Is that what you wanted?

Create few LOD{} calculation

[Avg 2017]

{FIXED DATEPART('year', [Order Date]):SUM(IIF(YEAR([Order Date])=2017,[Sales],NULL))}/

{FIXED DATEPART('year', [Order Date]):COUNTD(IIF(YEAR([Order Date])=2017,[Order Date],NULL))}

[Avg 2018]

{FIXED [Segment]:SUM(IIF(YEAR([Order Date])=2018,[Sales],NULL))}/

{FIXED [Segment]:COUNTD(IIF(YEAR([Order Date])=2018,[Order Date],NULL))}

Ratio:

[Avg 2018]/[Avg 2017]

workbook attached for your reference. Let us know if this help.

Mahfooj

• ###### 2. Re: Calculating between 2 LODs

Thanks Mahfooj

If I put in the order date, how can I show the ratio in the 2018 pane ?

Gavin

• ###### 3. Re: Calculating between 2 LODs

Thanks

Deepak

• ###### 4. Re: Calculating between 2 LODs

Hi Deepak

Thanks. How does this work ? Why did the previous solution show in 2017 only ?

If I want it to appear just in 2018 how would I amend it ?

Gavin

• ###### 5. Re: Calculating between 2 LODs

Here it is:

Thanks

Deepak

• ###### 6. Re: Calculating between 2 LODs

thanks again.... i just need the ratio to appear in the 2018 pane....

by the way, mind explaining your solution as well ?

• ###### 7. Re: Calculating between 2 LODs

Just use This Filter

LOOKUP(ATTR(Year(Order Date)),0)

and Take out 2017

and Please go Through LOD's I wrote..You will appreciate that it is difficult for us to go into details of each Step, but I think What I did in Calculations was straight forward. Please go Through tHem.

Thanks

Deepak