Modeling increase by group and year


#1

Hello,

I am developing a model to simulate the cost of coverage increases by 20% per year for 7 groups.

I am using a hierarchy slicer to enable the end users to select the group and start year (2019-2025) for the scale-up assuming a 20% coverage increase every year maxing out at year 5.

I want to create a measure to calculate a cumulative cost total.

My current measures are:

  1. 20% Implementation = SUMX(MASTER, [Total cost]*.2)

  2. Cumulative Total by year = CALCULATE([20% Implementation], FILTER(ALLSELECTED(MASTER), MASTER[Year]<= MAX(MASTER[Year])))

  3. Cumulative Total by Year MAX = IF ([Cum Total by year] < [Total cost], [Total cost], [Cum Total by year])

Measure 2 is calculating the amount for the year even when it is not selected, but when any other year for the group is. Any ideas on how I can adjust this measure to account for the year sub-selection?

Thanks much. Robert


#2

Robert-
I have a couple ideas but instead of going back and forth, can you supply some sample data?

Thanks,
Nick

Enterprise%20DNA%20Expert%20-%20Small


#3

Cost Model 1

Hello Nick,

Please find attached the link to my working file. As you can see on the first tab- “Cost Modeling V2”, I am able to control the costs by group and year, however the stacked column cumulative totals do not adjust accordingly for each year.

I was also wondering if you may know how to get my slicer to auto-select every year after the first year?

Any guidance or advice you may have is most welcomed!

Robert


#4

Nothing happens when I click the hyperlink to download. can you re-add?

Enterprise%20DNA%20Expert%20-%20Small


#5

Cost Modeling 2.6.2019.pbix (2.1 MB)

Cost Modeling 2.6.2019

Here it is. Thanks in advance for your kind assistance.

Robert


#6

@RKolesar
Please take a look at the attached files. There was a lot going on in your file, so I just grabbed the data, put it in excel, and imported to a new PBIX file. But I attached the excel file so you can look at what I did in Power Query. Just be sure to change the source in order to point to the correct location. With that being said here’s what I did so far:

  1. I broke out some of the data from the Master table you had. There is now DimVulnerableGroups, DimRateType, and DimCalendar

  2. DimCalendar will get the max and min of the master table to automatically build the calendar

  3. Added a “Total Cost” column in the FactMaster table, instead of having DAX do it.

I did those steps for a few reasons. The main is that it was hard for me to follow what exactly you were looking for and how to get there. And also, it’s better for performance if your fact table has keys (integers) instead of strings. Probably not a huge deal here, but always good to keep in mind. So here’s what the data model looks like:


Much easier to follow I think.

So with that in place I moved on to attempt to actually answer your question:thinking:
There are 4 measures:

  1. Sum of the total cost column from #3 above
  2. Total Cost * 20% (which we can make as a parameter so more flexibility, but did not do that yet)
    3/4. Cumulative totals of what is selected that does and does not reset every year.

Here’s what that looks like:

I wasn’t 100% sure what exactly you were looking for, but I think it’s a good starting point.

Here are the two files:
Cost Modeling Nick M.pbix (127.1 KB)

Tables.xlsx (16.4 KB)

Enterprise%20DNA%20Expert%20-%20Small


#7

Hello Nick,

Thanks for all your work on this. I am still working through the model restructure to make sure I understand it. It certainly looks much easier to manage.

However, I would like the overall cumulative totals to equal the individual totals by year. This is best illustrated in the graphic below:

The problem seems to start in Year 2022.
You can see it most clearly in the last stacked column for Year 2025: there are 3 sub-groups for $10M + $29M + $161M. When I use my hand calculator that totals to $200M. However, Power BI is calculating $207M. What do you think?

Robert


#8

image

I believe the problem is that when I select any Year within a group Power BI is adding the totals for all previous years for that group to the cumulative total instead of just adding the totals for the selected years. For example, in 2025 the individual group amounts are $17M + $29M + $9M = $55M however 2025 overall total is $67M. When I add the 3Q Self-employed values for all prior years that are not selected, it accounts for the difference. Thanks in advance for any guidance you can provide on how to limit the cumulative total to the selected years.


#9

I will take a closer look at this in the next couple of days. It’s a context / filter issue for sure, just have to mess around with it to see what shakes out.

Enterprise%20DNA%20Expert%20-%20Small


#10

Thanks much Nick. I am also interested to know how I can copy that date table you produced into a new data model. Could you also steer me in the right direction?


#11

Robert-
You are correct on the problem you are seeing. When you select different years for each Vulnerable Group you are creating, what we call, an asymmetric filter. Which means we need to change how calculate applies filters. Calculate will overwrite existing filters as the default but we can use KEEPFILTERS to tell DAX not to overwrite, but use Intersect. So there are two measures

  1. Gives the grand total by year for all the selected groups:

All Years Grand Total =
CALCULATE(
SUMX(
KEEPFILTERS(VALUES(DimCalendar[Year])),
SUMX(
KEEPFILTERS(Values(DimVulnerableGroups[Vulnerable Group])),
[Total Cost * 20%]
)
),
ALLSELECTED()
)

  1. Grand total for all Years and all Groups selected

All Years Grand Total =
CALCULATE( [Total Cost * 20%],ALLSELECTED() )

We then get this matrix:
Matrix%20with%20Totals%20by%20Year%20Group

2022 is where the filters become asymmetric, but keep filters accounts for that. Now the issue I am running into is to use that “Year Total by Group” and building a cumulative sum off of that. The pattern of getting a cumulative sum is to use Filter ( ALL (Date)… Once that ALL is put in, which removes filters, it gives the cumulative sum of all the years and all the Vulnerable groups selected. So 3q Self-employed will be in all the years not just 2022. And honestly this one is giving me some trouble. Messed around with it a little yesterday but going to look at a little bit more because it’s bothering me I cannot figure this out. There’s a way to do it, just have to think about it some more.

But in the meantime, I attached the updated PBIX file. If you go to PQ, you will two parameters, one where you will enter in the File Location of the excel file and then another one where you will enter in the file name. In doing that you will be able to click into the DimCalendar table created. Go through the applied steps of that table and you will see how the calendar was formed.
PQ%20File%20Parameters

File:
Cost Modeling Nick M.pbix (156.9 KB)

-Nick

Enterprise%20DNA%20Expert%20-%20Small


#12

Hello Nick,

Eureka! I got it working!!! However, there is a new crinkle, so I am not out of the woods yet…

The trick is to use the Calendar/Date Table to calculate the cumulative total for each selected group in a year:

Cumulative Total = 
CALCULATE( 
        [Total Cost Sum], 
        FILTER( 
            ALLSELECTED(Dates),
            MAX(Dates[Year]) >= Dates[Year]
        )
    ,
        ALLSELECTED('Vulnerable Groups')
)

And, then use the MASTER FactTable date variable to calculate the annual cumulative total.

Incremental Cost by Year = 
CALCULATE( 
        [Total Cost Sum], 
        FILTER( 
            ALLSELECTED(MASTER),
            MAX(MASTER[Year]) >= MASTER[Year]
        )
    ,
        ALLSELECTED('Vulnerable Groups')
)

As a final step, I reintroduced a MAX on the increase for the cumulative total by group measure. Basically, the costs increase by 20% per year and (mostly) level off after 5 years exempting a small population increase. This also seems to be working well.

Now, I need to do the same for the second measure so the annual cumulative total matches the MAX cumulative total by group. So, we are kind of back to the drawing board… However, I have a feeling that you will know the trick.

Here is my new and improved .pbix file with some other upgrades. Thanks in advance for any and all ideas you may have.

Robert

RKolesar Cost Model


#13

@RKolesar
Awesome man! That was driving me insane as I knew there had to be a way. But there was a point where I was going down the rabbit hole and just seeing if anything made sense (which I would not! suggest doing :sunglasses:

I will take a look at what you got in the next few days but that looks like it makes sense

Enterprise%20DNA%20Expert%20-%20Small