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.