Sum of $ Amount Totals Over Multiple Years

[WinterMist]

Hello Community - 

Any help on this DAX calculation would be greatly appreciated.

The PBIX is shared here.

https://drive.google.com/file/d/1BNmF6x3XW1oCaONl9Mz8KY8uEw2fD_dS/view?usp=drive_link

Currently, the report is working as desired, except for 1 measure.

I am unable to successfully calculate measure [01D Premium YTM Adj - Mult Years].

Important Notes for Table Visual "Summary - Main":

- Table Visual is filtered to only include records WHERE [T1&T2] = 1.

   - [T1] returns 1 only if "Avg Loss Ratio" > "Program Enrollment %" slicer selected value.

   - [T2] returns 1 only if "Loss Ratio YTM" > "Loss Ratio Trigger %" slicer selected value.

- "Adtl Loss Adj Exp" column is calculated using the "Additional Loss Adjustment Expense" slicer selected value.

- Both " Loss Ratio YTM" & "Premium YTM" are calculated based on the "Year to Model" slicer selected value.

Desired Outcome #1:

The top right card visual needs to correctly display the Total "Adtnl Loss Adj Exp" across multiple selected years.

- This can either be a static number of years (e.g. 10 years hard-coded into the measure)

- OR it should be the selected years in the "Year" slicer

For example, if I keep slider slicer values constant (150%, 100% & 10% respectively) and only change "Year to Model",

the correct total $ for each YTM (Year to Model) is as follows:

2021 = $30,835,812

2022 = $78,723,649

2023 = $71,350,756

2024 = $69,025,807

The new card visual needs to TOTAL all of these $ values over all the selected Years (or hard-coded years) in the "Year" slicer.

For example, if the Year slicer theoretically had only these 4 years selected, then the card visual needs to show the SUM of these 4 years.

$30M + $78M + $71M + $69M = $249.9M

NOTE: What's confusing here is that the "Year to Model" slicer is being used to correctly calculate multiple columns in the "Summary - Main table", which then impacts which rows are included/excluded, and thereby impacts the total $ amount calculation.  However, this new card visual needs to SUM up these same YTM $ amounts for every year in the "Year" slicer (or a hard-coded # of years in the measure).   If ALL years are selected in the "Year" slicer, then the card should show the total $ amount for all years.

Desired Outcome #2:

The same calculation in the new card visual above needs to be broken out in a table visual by Year.

Once again, in the example below, I'm pretending that only 4 years are selected (2021-2024).

However, we need to do this:

- Either across all years selected in the "Year" slicer

- OR by a hard-coded number of years in the measure (e.g. last 10 years)

Very thankful for any help on this one,

Nathan

[v-saisrao-msft]

Hi @WinterMist 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[WinterMist]

@v-saisrao-msft 

Thanks again for taking the time to respond.

Unfortunately, your solution is not what is needed.

Your solution shows only a single value in the lower table visual.  (Screenshot 1 below).

Also, it does not show the Year column.

Also, it only displays the value for the Year to Model.

This is not correct.

I need the lower table visual to show 2 columns: Year & $ Amount.  (Screenshot 2 below)

And it needs to display rows for ALL years; not the Year to Model only.

Thanks again for your time.

Nathan

[v-saisrao-msft]

Hi @WinterMist,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @WinterMist,

Below is the updated DAX measure 

With this DAX, you will match both the card and table visuals.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[v-saisrao-msft]

Hi @WinterMist,

The table visual used a simple row-level calculation for Premium YTM Adj, which included all rows and all crops, without applying key business rules like T1&T2 filters or deduplication. This caused the total in the table to differ from the card.

Now, both the card and the table use the same enhanced logic:

• They summarize by unique State, County, and Crop.
• Include only the 8 approved crops.
• Apply T1&T2 condition = 1.
• Respect slicers like Year To Model, Expense Adjustment, and T1&T2 thresholds.

This alignment ensures that both visuals show consistent, accurate business-aligned numbers.

i have just created 01C Premium YTM Adj – Card Logic measure in table visual and added it in duplicate page.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[WinterMist]

@v-saisrao-msft 

Thank you very much for taking the time to respond!

This looks very similar to the solution for the card visual I shared earlier in this thread (Message 6).

I have pasted in the screenshot again of the working code below (50% of the solution).

NOTE: SelectedYears cannot be pulled from 'D Year to Model'[Year to Model] because this is a single select slicer, and we need to iterate over multiple years.  We actually need to ignore the selected value from this slicer and inject a series of years to simulate multiple YTM values.  (The measure pasted below is doing this successfully.)

PROBLEM: Although the measure shown below works for the card visual, it does NOT work for the table visual.  The table visual needs to have 2 columns: breaking down the card visual total amount by Year & Premium Adj.  (See Excel screenshot below)

Again, thanks for taking the time to respond, but I'm still not able to break down the total by year in a table visual.  If you were able to do this in the PBIX, I do not understand how.

Thanks again,

Nathan

[v-saisrao-msft]

Hi @WinterMist,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft]

Hi @WinterMist,

Thank you for reaching out to the Microsoft Fabric Forum Community.

I have implemented the DAX logic for this thread, and it is working as expected. Kindly review it and let me know your feedback.

01D Premium YTM Adj - Mult Years_Multiselect =
VAR SelectedYears =
    VALUES ( 'D Year to Model'[Year to Model] ) -- Assuming this is your slicer table
 
VAR vPremiumAdj_MultYears =
    SUMX (
        SelectedYears,
        VAR CurrentYear = [Year to Model]
        VAR vSummarizedTable =
            CALCULATETABLE (
                SUMMARIZECOLUMNS (
                    'D County'[State Name],
                    'D County'[ANSI County Name],
                    'D Crop'[Crop Name],
                    "Loss Ratio AVG", [03A Loss Ratio],
                    "Loss Ratio YTM", [03B Loss Ratio - YTM],
                    "Premium YTM", [01B Premium YTM],
                    "Premium ADJ", [01C Premium YTM Adj],
                    "T1&T2", [09 T1&T2]
                ),
                'D Year to Model'[Year to Model] = CurrentYear  -- Force context for each year
            )
        RETURN
            SUMX (
                vSummarizedTable,
                IF ( [T1&T2] = 1, [Premium ADJ], 0 )
            )
    )
RETURN
    vPremiumAdj_MultYears

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[WinterMist]

@Amar_Kumar 

I understand conceptually what you are recommending, but I cannot make it work.

Are you actually able to do this succesfully within the PBIX?

1) My DAX measure definitely works within the card.  100% good.

2) However, when I try to re-create similar logic within the DAX Table (for use in the new table visual), it simply does not work.

Here is the working DAX measure.

Correct 4 year result = $249M.

It's working...

Now I am trying to create the DAX calculated table that includes 2 columns:

1) InjectedYear

2) Premium Adj

DAX calculated table as follows:

Unfortunately, as you can see, "Data View" shows that "Premium Adj" = 0 for all 4 years.

I have tried several variations of this DAX table and they all show "Premium Adj" = 0.

If you could either do this within the PBIX, or point out where I am going wrong, I'd appreciate it.

Thanks,

Nathan

[WinterMist]

@Amar_Kumar 

Man, this is tough.  Been trying to implement your guidance, but without success.

Keep making minor adjustments, but nothing seems to work....yet.

Will keep trying tomorrow.

Thanks,

Nathan

[WinterMist]

@Amar_Kumar 

Just had a breakthrough - I'm 50% of the way there.

1) The Card Visual total across all 4 years is now correctly returning $249M.

2) The Table Visual summary that breaks down the $249M across all 4 years is not yet working.

   - Still haven't figured this part out.

Here is the solution that's currently working for the Card Visual correctly returning $249M.

Thrilled that the Card Visual is working.

Still stuck on the Table Visual.

Any help on that part would still be greatly appreciated.

Regards,

Nathan

[Amar_Kumar]

You can with this summarized table measure for table visual.

01E Premium YTM Adj - Mult Years Breakdown =

VAR vYearList =

    ADDCOLUMNS (

        GENERATESERIES (2021, 2024, 1),

        "Year", [Value]

    )

RETURN

    ADDCOLUMNS (

        vYearList,

        "Amount",

        CALCULATE (

            [Premium ADJ], -- or the adjusted measure logic

            REMOVEFILTERS ( 'D Year to Model' ),

            'D Year to Model'[Year to Model] = [Value]

        )

    )

Alternately you can also created a calculated table for the table visual.

[WinterMist]

@Amar_Kumar 

Not sure, but perhaps this might be helpful in understanding where I am stuck.

Below is my attempt at the new measure.

As you can see, the Card Visual works in the first half of the measure, but not the second.

- It correctly returns $30.8M for YTM 2021.  (VAR 1)

- But incorrectly returns $123M (instead of $249M) for all 4 YTM years (2021-2024).  (VAR 2)

NOTE 1: In this version of the measure, I am returning VAR vPremiumAdj_CurrentYTM.

This is just done as a checkpoint test to make sure it's working before going farther - and it is working.

NOTE 2: As mentioned previously, SummaryMain is not a table in the model; so I have to recreate it as a CALCULATETABLE VAR called vSummaryMain.

Now comes the problem where I am stuck.

For test purposes, I am hard-coding the same 4 years in the measure that we previously discussed. (2021-2024)

Now my goal is to iterate over these 4 years, treating each year as the YTM to correctly calculate and return the SUM total of $249.9M.

Unfortunately, my code is incorrectly summing only the $ amount for the currently selected year in the "Year to Model" slicer (in this case 2021 = $30.8M) and multiplying it by 4, and wrongly returning ~ $123M (instead of the correct $249M).

CORRECT: $30M + $78M + $71M + $69M = $249.9M

INCORRECT: $30.8M + $30.8M + $30.8M +$30.8M = $123M

Below the measure is updated to RETURN vPremiumAdj_MultYears.

SOLUTION CHALLENGE: VAR vPremiumAdj_CurrentYTM needs to be able to IGNORE the YTM slicer value, and calculate each year as a separate YTM.  But I am not sure how to do this.  How do I get VAR vSummaryMain & VAR vPremium_Adj_CurrentYTM to calculate with an ITERATING/CHANGING YTM from VAR vYTMYearsTable?

Again, not sure if this is helpful, but this is where I am stuck.

Regards,

Nathan

[WinterMist]

@Amar_Kumar 

Thank you for taking the time to respond.

Unfortunately, there are several issues with what you are recommending.

1) I noticed that your DAX regularly references 'Summary - Main' as if it's a table in the model.  Sorry if this was not clear, but it's not a table in the model that can be referenced in DAX.  It's only a table visual.

2) Your first measure appears to be calculating Adtl_Loss_Adj_Exp as:

    'Summary - Main'[Loss Ratio YTM] * [Selected_Adtl_Loss_Adj_Exp_Value]

But this is not correct.  Loss Ratio YTM is not part of the calculation.

3) Your 2nd DAX measure is calculating across ALLSELECTED [Year to Model].  But this doesn't make sense.  Year to Model is a single-select slicer that can only hold 1 year at a time, but I need the measure to calculate across multiple years, as previously mentioned.

Rather than go back and forth again, do you think you could just show me by implementing what you are suggesting in the PBIX I shared and then share it back with me?

Regards,

Nathan

[Amar_Kumar]

Sure @WinterMist I'll revert with the pbix fil.

[Amar_Kumar]

1. Base Measure: Adtl Loss Adj Exp

This assumes you already have a calculated measure like:

Adtl_Loss_Adj_Exp = 

SUMX(

    FILTER(

        'Summary - Main',

        'Summary - Main'[T1&T2] = 1

    ),

    'Summary - Main'[Loss Ratio YTM] * [Selected_Adtl_Loss_Adj_Exp_Value]

)

Replace [Selected_Adtl_Loss_Adj_Exp_Value] with the measure or parameter that captures the slicer's selected value.

2. Final Measure: Total Across Selected Years

Create this measure:

Total_Adtl_Loss_Adj_Exp_AllYears = 

CALCULATE(

    [Adtl_Loss_Adj_Exp],

    ALLSELECTED('Summary - Main'[Year to Model])

)

If your Adtl Loss Adj Exp is already being calculated per YTM, and you want to total those across selected years:

Total_Adtl_Loss_Adj_Exp_AllYears = 

SUMX(

    VALUES('Summary - Main'[Year to Model]),

    [Adtl_Loss_Adj_Exp]

)