Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Add column to matrix visual

Hello,   I am working with data like this:   Division DivisionNumber Type Amount P 1 Available 100 M 2 Available 400 V 3 Requested 200 P 1 Available 300 P 4 All...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous

     

    This might be a bit of an unnecessarily intricate solution, certainly compared to Zubair_Muhammad's, but you could try:

     

    1. Create a new, table DisplayTable with one column with the values of your Type column plus one for the additional column "Available+Requested". Create a relationship between DisplayTable[Type] and Table1[Type] (Table1 is your current table)

    2. Place DisplayTable[Type] in the columns of your matrix visual (the rows are the same as you have now)

    3. Create this measure:

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ),
    CALCULATE (
        SUM ( Table1[Amount] ),
        Table1[Type] IN { "Available", "Requested" }
    )
)

    4. Place the measure in values of your matrix visual

     

                     

  • AlB's avatar
    AlB
    7 years ago

    Anonymous

    Most likely nothing wrong :smileyhappy: I hadn't tested it.

    Try this small change:

     

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ),
    CALCULATE (
        SUM ( Table1[Amount] ),
        DisplayTable[Type] IN { "Available", "Requested" }
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlB

It worked, thanks so much!

 

Is there any possibility to add two more columns based on the column we just calculated?

First one should be: Allocated - (Available+Requested)

Second one: (Available+Requested) / Allocated * 100

AlB's avatar
AlB
Community Champion
7 years ago

Anonymous

Yes, we can do it by following the same logic but the more columns you add, the less sense it makes to do it this way and the more with one different measure for each calculation. The code is getting overly long now, I would think.

 

You'd need to add the new column names to the DisplayTable. I've used the exact copy of how you've described the operations. You can update that if you want with something shorter but then you'd have to update the corresponding comparisons in the code as well. The new DisplayTable would now look like:

 

Type

Available
Requested
Allocated
Available+Requested
Allocated - (Available+Requested)

(Available+Requested) / Allocated * 100

 

and we need to make some changes in the measure:

 

Measure 2 = 
VAR _CurrentColumn = SELECTEDVALUE ( DisplayTable[Type] )
RETURN
    SWITCH (
        TRUE ();
        _CurrentColumn IN ALL ( Table1[Type] ); SUM ( Table1[Amount] );
        _CurrentColumn = "Available+Requested";
        CALCULATE (
            SUM ( Table1[Amount] );
            DisplayTable[Type] IN { "Available"; "Requested" }
        );
        _CurrentColumn = "Allocated - (Available+Requested)";
        CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] = "Allocated" )
            - CALCULATE (
                SUM ( Table1[Amount] );
                DisplayTable[Type] IN { "Available"; "Requested" }
            );
        _CurrentColumn = "(Available+Requested) / Allocated * 100";
        100*DIVIDE (
                CALCULATE (
                    SUM ( Table1[Amount] );
                    DisplayTable[Type] IN { "Available"; "Requested" }
                );
                CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] = "Allocated" )
            )
    )

I've interpreted  (Available+Requested) / Allocated * 100 to be

100*( (Available+Requested) / Allocated) 

If this is not the case you can update the code with the correct calculation

 

 

Code formatted with  

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    AlB

    Wow, thanks again, this works absolutely fine.

    I don't need any other columns, so there is nothing to complain about.

    I really appreciate your help, thank you! :smileyhappy: