Calculate an index measure with 2017 as denominator

[Anonymous]

Hello everyone,

I have run into a problem when I'm building a dashboard for my company.

I will try my best to simplify the explaination of my problem.

To start, I have three simple measures:

Lines = Sum(Lines)

Man hours = Sum(Man hours)

Productivity = Lines / Man hours

In the end I want to see a 2019 graph that shows INDEXED productivity on monthly basis, with total 2017 as the base value. This seems difficult to achieve, since the visual itself is not displaying 2017 and I get infinity values.

So for this index measure, I obviously want to have 2017 in the denominator. 

The base value should be the sum of (Lines 2017 / Man hours 2017)

That denominator number is constant at 8,21, but I'd prefer to come up with the real solution instead of typing it manually.

In the end, I'm looking for something like:

Monthly index value = Productivity / Productivity (with 2017base, i.e value 8.21).

But all my DAX calculations give me graphs of with infinity values, because I am plotting rolling 12-months where 2017 is filtered out.

What am I fundamentally missing here?

[Anonymous]

can you upload some sample data with what your expected outcome should be?

Also, I'd use the DIVIDE function instead of "/"

[Anonymous]

Hi Nick! Thanks for answering. 

Of course, I put together this excel file to demonstrate my desired outcome as easily understandable as possible.

It seems my issue is that I do not know how to create the "denominator" for my desired measure.

The first (and so far my only idea) I tested to create was:

[Lines/Manhour]

/

CALCULATE([Lines/Manhour];Time_Lookup[YEAR]="2017")

But that resulted in infinity values.

[CheenuSing]

Hi @Anonymous ,

Can you upload the excel file to Google or One Drive and paste the link here.  It will help load the data faster for testing.

Cheers

CheenuSing

[Anonymous]

Certainly, hope this one works: 

https://drive.google.com/file/d/1nAsiklfOuACnDqkrxl1vlg6-DsLeGUYg/view?usp=sharing

[CheenuSing]

Hi @Anonymous 

Some more clarification.  Will the data be always for current year plus last 2 full years.

(a) That is in 2020 July the dataa will be for 2018 and 2019 whole year and 2020 upto July.

Or (b)  it will be 2017,2018,2019 whole years and 2020 upto July.

In other words your Cell N14 should be calculated as the sum of Lines divided by Sum of ManHours of the min year value of data and that should be used for computing Productivity Index.

So for (a) above it should be SUmof Lines / Sum Of Manhours for 2018

and (b) above should be sumof Lines / Sum of Manhours for 2017.

Kindly confirm

Cheers

CheenuSing

[Anonymous]

Hi @CheenuSing  ,

Regarding the calculation of cell N14. For now, we will always use [(Sum of Lines 2017) / (Sum of MH 2017)] as our denominator base to calculate the index (almost like a stock market that has an index from way back). 

This ratio is based on the full year of 2017 ( = 8,21 for 2017). 

However, each month we receive new data with monthly granuality. However, that is not a big problem since we are always calculating the index with ratios.

So for example, if I receive in 2019 August new data:
Lines 1,000,000 and MH 130,000. That gives me an August 2019 ratio of 7,69.

This I will divide with 8,21 and get the productivity index of 0,93 for August 2019.

I hope this helped with clarifying! 

[CheenuSing · Accepted solution]

Hi @Anonymous ,

Using the data provided I have done the solution to meet your need.

The solution is built using both DAX Measure and Calculated Columns.

Steps dones.

1. After loading the table provided in excel (H8..J35)

2. Using Query Editor added Index Column to get the final output of Time sorted to the Index.

3. Created Calculated Columns using New Columns when you right click on the table in the report view.

a)  Year,

b) Min Year,

c) SumLines,

d) SumManhours,

e) BaseIndex

f) ProductivityIndex

4. Created measures

      - DAX_MInYear

     -  DAX_SumLines

    -  DAX_ManHours

    - DAX_ BaseIndex

    -DAX_ProductivityIndex

5. Plotted the values as a table from the visualisation pane.

I am attaching the pbix for your reference.

Cheers

CheenuSing

[Anonymous]

Dear @CheenuSing ,

I have looked at your attached report and with help from it, I have managed to apply the necessary steps to finally get the solution I needed.

The problem was definitely in the fact that I was unable to get the DAX's right to sum the lines and man hours on 2017. The "MIN year" method worked great for this. After having my base index ready on every month, it was quite straightforward.

I am truly grateful for your support. You saved my day. Thank you!