Using HASONEFILTER/SUMX to correct subtotals makes forcast totals disappear
Howdy Friends, Here is an odd problem for this Friday: Background I have a data model where I am forecasting out to 2030, the number of minutes on different types of medical imaging equipment based on the historical growth rates in the data. My first step was to summarize the data set into a SummaryMinutes table that is grouped the way I want to do the forecast (grouping by [VolumeSort], [Modality], and [FiscalYear]). This gives me a nice tidy table to work with. The second step I did was to calculate the compound annual growth rate in the summary table between 2018 and 2022. There are a few measures to make this work but the result is in [CAGR Historical]. Next, the client would like to override the growth rate for three of the four [VolumeSort] groupings and leave the historical growth rate for the "Other" category. I did this with a SWITCH function in the [CAGR Future] measure. CAGR Historical = // calculated growth rate from 2018 to 2022 VAR Periods = 2022 - 2018 + 1 RETURN RRI ( Periods, [Minutes FY18], [Minutes FY22] ) CAGR Future = // override the growth rate for 3 of the 4 [Volume Sort] groupings, keep the calculated growth rate for the "Other" [Volume Sort] grouping CALCULATE ( SWITCH ( SELECTEDVALUE ( SummaryMinutes[Volume Sort] ), "Cancer IP", 0.051, "Cancer OP", 0.111, "Emergency", 0.040, [CAGR Historical] ), ALL ( LookupFiscalYear ) ) The Problem The third step is where the trouble starts. I created a measure called [Minutes Forecast] that uses the FV function and the growth rates from [CAGR Future] to forecast the minutes out to 2030. The forecast values are correct and as expected the subtotals do not add up correctly. To fix the subtotals I created a measure called [Minutes Forecast Subtotal] where I used the HASONEFILTER/SUMX of VALUES pattern to correct the subtotals. I am grouping the results on two levels, [VolumeSort] and [Modality]. Therefore I nested the HASONEFILTER/SUMX pattern to make the totals work across both levels. However, instead of correctly summing subtotals, the subtotals disappeared for all the future forecasted values! I've used this technique many times in the past but I can't workout out what is making the subtotals disappear in this model. Minutes Forecast = // forecast total minutes from 2023 through 2030 using [CAGR Future] growth rates - FV ( [CAGR Future], SELECTEDVALUE ( LookupFiscalYear[FiscalYear] ) - 2022, 0, [Minutes FY22] ) Minutes Forecast Subtotal = // correct subtotals at the [Modality] grouping level IF ( HASONEFILTER ( SummaryMinutes[Modality] ), // nested correct subtotals at the [Volume Sort] grouping level IF ( HASONEFILTER ( SummaryMinutes[Volume Sort] ), [Minutes Forecast], SUMX ( VALUES ( SummaryMinutes[Volume Sort] ), [Minutes Forecast] ) ), SUMX ( VALUES ( SummaryMinutes[Modality] ), IF ( HASONEFILTER ( SummaryMinutes[Volume Sort] ), [Minutes Forecast], SUMX ( VALUES ( SummaryMinutes[Volume Sort] ), [Minutes Forecast] ) ) ) ) Results As you can see in these two report pages, if just use the [Forecast Minutes] I get the wrong subtotals as expected. But on the second page, you can see that my corrected subtotals pattern makes the subtotals only for future values disappear! Here is a Box link for the PowerBI file: https://app.box.com/s/e1bas1vj2szjxke3zo2ppskq6vihzcwl Thanks for any help!
