Setting calculation level of DAX formulas, independent of what is shown in visual

[teefy]

Hi,

I am calculating a delta between 2 columns, and classifying them as either an Increase or Decrease. This is all done as Measures, not columns, since the Value Date 1 & 2 are dynamically adjusted by date slicers on the page.

This works fine in a detail table - e.g. at a Material and Plant level:

I've created a slicer to filter the Delta Type (Increase/Decrease), and have a visual level filter that is driven by the slicer selection to include/exclude data accordingly using another measure. E.g. if I filter for 'Decrease' this works as expected:

Where I'm stuck is if I want to summarize at a different level, e.g. Plant only and I want to review the impact of only Decreases. As soon as I take 'Material' out of the table, the measures calculate at a different level, giving the net overall delta:

This isn't what I want to see -- having sliced to show 'Decreases' I don't want the increases being considered in the calcualtions.

What I'd like the table to show is the following, with only the 2 'Decrease' lines being shown at a summary level of Plant without Material:

Can anyone please help point me in the correct direction? I've tried various approaches, but can't seem to get it to calculate as needed.

Sample PBIX File 

Many thanks in advance for any support!

[talespin · Accepted solution]

Hi @teefy ,

Thank you for sharing pbix file.

You need to incorporate Delta type slicer value into your calculation for value measures. Please note that it will only work for Material and Plant, if you add any other column to visual, your measure might not give correct results and you may need to modify SUMMARIZE to account for that. Hope this helps.

I am grouping by Material and Plant, checking value2-value1 and then calculating which value to include/exclude and at end SUM. Also I assume you understand what you are doing, some times calculation shouldn't be additive, that depends on what the business rules is, how you want to see that numbers.

Value Date 1 =

VAR _DeltaType = SELECTEDVALUE('Value Delta Direction'[Value])

RETURN

SUMX(

        ADDCOLUMNS(

                    ADDCOLUMNS(

                                SUMMARIZE('Table', 'Table'[Material], 'Table'[Plant]),

                                "VDt1",CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 1'[Report Date])),

                                "VDt2",CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 2'[Report Date]))

                    ),

                    "Delta", [VDt2] - [VDt1]

        ),

        if ( _DeltaType = "Decrease", if( [Delta] < 0, [VDt1], BLANK()),  if( [Delta] > 0, [VDt1], BLANK()) )

)

Value Date 2 =

VAR _DeltaType = SELECTEDVALUE('Value Delta Direction'[Value])

RETURN

SUMX(

        ADDCOLUMNS(

                    ADDCOLUMNS(

                                SUMMARIZE('Table', 'Table'[Material], 'Table'[Plant]),

                                "VDt1",CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 1'[Report Date])),

                                "VDt2",CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 2'[Report Date]))

                    ),

                    "Delta", [VDt2] - [VDt1]

        ),

        if ( _DeltaType = "Decrease", if( [Delta] < 0, [VDt2], BLANK()),  if( [Delta] > 0, [VDt2], BLANK()) )

)

[teefy]

Thank you @talespin so much for your solution! I made a tiny tweak to the final part, and its working really well. I really appreciate your insights and the time taken to look into this for me.

Value Date 1 = 
VAR _DeltaType = SELECTEDVALUE('Value Delta Direction'[Value])
RETURN
SUMX(
    ADDCOLUMNS(
        ADDCOLUMNS(
            SUMMARIZE('Table', 'Table'[Material], 'Table'[Plant]),
            "VDt1", CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 1'[Report Date])),
            "VDt2", CALCULATE(SUM('Table'[Value]), USERELATIONSHIP('Table'[Report Date], 'Compare Date 2'[Report Date]))
        ),
        "Delta", [VDt2] - [VDt1]
    ),
    SWITCH(
        TRUE(),
        ISBLANK(_DeltaType), [VDt1],
        _DeltaType = "Decrease", IF([Delta] < 0, [VDt1], BLANK()),
        _DeltaType = "Increase", IF([Delta] > 0, [VDt1], BLANK()),
        [VDt1]
    )
)

[talespin]

@teefy 

You're welcome.

[Greg_Deckler]

@teefy You sort of have the reverse of this problem but this should show you the basics of how to get your DAX to calculate at a different level of granularity. Bottom line, the trick is to use ALL or ALLSELECTED to essentially remove the internal visual context filters and then you impose your own filter context back on the table.

Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community