Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aggregate Matrix Based on Filters and Measures

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?

 

 

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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",
)
  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.