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

Hi orihait , I would create the next calculated columns:

 

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:

 

 

Best Regards,
Miguel

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

 

 

orihait's avatar
orihait
Helper I
7 years ago

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!

 

 

  • 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.