Aggregate Matrix Based on Filters and Measures

[Anonymous]

I am trying to create a calculated matrix in Power BI.

Here is my source data:

Here is what I am able to get to currently.  Note Balance_1 is based on filtering to Version One.  Balance_2 is based on filtering to Version Two.  The users need to be able to filter between versions.  I then do a measure calculation in the explanation column.

How can I then summarize this view similar to the below?

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

1. Enter data to make an Explannation table.

2. Sort by [Explanation] column by [Order] column.

3. Create measures like so:

Balance_1_Sum = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Customer],
        'Table'[ID],
        "Balance1", [Balance_1],
        "Balance2", [Balance_2],
        "Explanation_", [Explanation]
    )
RETURN
    SUMX (
        FILTER ( t, [Explanation_] = SELECTEDVALUE ( Explanation[Explanation] ) ),
        [Balance1]
    )

Delta_Sum = 
VAR t =
    SUMMARIZE (
        'Table',
        'Table'[Customer],
        'Table'[ID],
        "Balance1", [Balance_1],
        "Balance2", [Balance_2],
        "Explanation_", [Explanation],
        "Delta_", ABS ( [Balance_2] - [Balance_1] )
    )
RETURN
    SUMX (
        FILTER ( t, [Explanation_] = SELECTEDVALUE ( Explanation[Explanation] ) ),
        [Delta_]
    )

4. Create a Table visual like so:

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create two tables like this

Version 1 = distinct(Table[Version])

Version 2 = distinct(Table[Version])

Do not join it with table back

Try like

Balanace_1 = 
var _max = allselected(Version1[Version]) // or Values(Version1[Version])) // or selectedvalues(Version1[Version]))
return
calculate(sum(Table[Balance]),Table[Version] in _max)


Balanace_2 = 
var _max = allselected(Version2[Version]) // or Values(Version1[Version])) // or selectedvalues(Version1[Version]))
return
calculate(sum(Table[Balance]),Table[Version] in _max)


diff =
Switch(Ture(),
[Balanace_1] - [Balanace_2] =0 ,"No Change",
[Balanace_1] - [Balanace_2] >0 ,"Increase",
[Balanace_1] - [Balanace_2] <0 ,"Decrease",
)