Forum Discussion

orihait's avatar
orihait
Helper I
7 years ago

Using sumif equivalent in a matrix visualization

hi guys, i've got a matrix visualization that calculates the sum of standing loans and approved loans by "fund". it looks something like this and uses a very simple sum measure.     fund c...
Need Help
Tips and Tricks
ZunzunUOC's avatar
ZunzunUOC
Resolver III
7 years ago

As far I know, you can create the matrix but not calculated like you need (fund a=fund a+ fund c/2 and fund b=fund b + fund c/2.

 

You would need calculate to achieve this.

MFelix's avatar
MFelix
Super User
6 years ago

Hi orihait ,

 

Don't know if this answer is still on time but try the following:

  • Create a table with the participation percentages:

Fund Parent Percentage

C A 50%
C B 50%
A A 100%
B B 100%
D D 100%

 

  • Add the following measure:

 

Measure = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Participation; Participation[Parent] );
        "@Total"; CALCULATE (
            SUM ( Projects[Total] );
            FILTER ( Projects; Projects[Fund] = Participation[Parent] )
        )
    );
    [@Total]
)
    + SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Participation; Participation[Fund]; Participation[Percentage] );
            "@Total"; CALCULATE (
                SUM ( Projects[Total] ) * SELECTEDVALUE ( Participation[Percentage] );
                FILTER (
                    Projects;
                    Projects[Fund] = Participation[Fund]
                        && Participation[Percentage] <> 1
                )
            )
        );
        [@Total]
    )

 

Now create your matrix with the Parent column on the previous created table as columns, status on rows and the measure on values.

 

should give expected result.