Population Mix Adjustment

[JeremiahMason]

I have population-adjusted costs from CY18 to CY19 and my goal is to use the CY19 population distribution on the CY18 data to put them on an apples-to-apples basis. I.e. I have per-person costs for each of CY18 and CY19, and I want to multiply the CY18 per-person costs with the CY19 people.

Simply put, I need two measures - one which varies normally by year and the other which returns the CY19 membership regardless of year.

My PBI is set up with a master table which consolidates my different cost and membership sources such that all my visuals are built off of it. My key between the eligibility and master table (one to many) includes CY and a number of other variables. From what I've read online, it seems like I should be able to define two relationships between my master and eligibility table, one for my regular membership relationship (active) and another which keeps the CY19 portion static between both years so the CY18 rows on my master table will also pull CY19 membership (inactive). Then the UseRelationship function is supposed to be able to use the inactive relationship in a measure to give my desired result, but it's just returning nulls.

Does anyone have experience making this sort of calculation work? Thanks in advance

[v-cazheng-msft]

Hi @JeremiahMason

Is your problem solved? Is the last reply of you the solution? If it is, could you kindly mark it as the solution?

Caiyun Zheng

Best Regards 

[selimovd]

Hey @JeremiahMason ,

your explanation is understandable but a little abstract.

Maybe you can post the tables and data model and give an example how the result should look like. This makes it easier to help. Best case would be if you could share the file if the content is open to share.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[JeremiahMason · Accepted solution]

I got it figured out - you can, in fact, do what I was trying to do by defining an inactive relationship holding the CY portion of the calculation constant and use the UseRelationship function to force the measure to use the inactive relationship. My issue was caused by my key field used in the relationship having an error.