Using sumif equivalent in a matrix visualization

Question

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.&nbsp;&nbsp;fund c is acutally a co investment by fund a and fund b so i'd like to refine the measure so that the output would ignore fund c and add 50% of it's balance to fund a and fund b.the result should look like this :&nbsp;&nbsp;can anyone think of a method to achieve this output?thanks!!!!.&nbsp;&nbsp;

zunzunuoc · Answer

Hi orihait , I would create the next calculated columns:&nbsp;fund ab = 
var VarValue=Table1[status]
return
(LOOKUPVALUE(Table1[fund c];Table1[status];VarValue)/2)+Table1[fund a]fund bb = 
var VarValue=Table1[status]
return
(LOOKUPVALUE(Table1[fund c];Table1[status];VarValue)/2)+Table1[fund b]You would get your data:&nbsp;&nbsp;Best Regards,MiguelIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;&nbsp;

zunzunuoc · Answer

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.&nbsp;You would need calculate to achieve this.

orihait · Answer

hi and thanks again for taking the time to help.

is there any way i could do it without creating new collumns i.e by using the matrix's filter context?

the raw data looks something like this

and should look like this on the matrix :

i figured i could use the visualization filter to edit fund c out and show only the ones i want.

thanks again! i highly appreciate it!

mfelix · Answer

Hi&nbsp;orihait&nbsp;,
&nbsp;
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%

&nbsp;

Add the following measure:

&nbsp;
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]
                        &amp;&amp; Participation[Percentage] &lt;&gt; 1
                )
            )
        );
        [@Total]
    )
&nbsp;
Now create your matrix with the Parent column on the previous created table as columns, status on rows and the measure on values.
&nbsp;
should give expected result.

Forum Discussion

Using sumif equivalent in a matrix visualization

4 Replies

Related content

Matrix Visual Duality

Custom Matrix Visual

Power Matrix visual

Formatting Matrix Visuals

Matrix visual