Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Add column to matrix visual

Hello,

 

I am working with data like this:

 

DivisionDivisionNumberTypeAmount
P1Available100
M2Available400
V3Requested200
P1Available300
P4Allocated800
M5Allocated200
P1Available100
V3Requested300
V2Allocated600

 

Out of this table I created a matrix visual:

https://imgur.com/a/KjgFOsc

 

Now I would like to add a column to that matrix which sums up the two columns "Available" and "Requested" for each row.

 

Is that possible? If not, how could I mainpulate the data before making the visual (I think I would need to add calculated rows to solve this but that is not possible, right?).

 

Thanks in advance.

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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

 

                 

View solution in original post

@Anonymous

Most likely nothing wrong Smiley Happy 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" }
    )
)

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@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

@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   www.daxformatter.com

Anonymous
Not applicable

@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! Smiley Happy

AlB
Super User
Super User

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

 

                 

Anonymous
Not applicable

https://imgur.com/a/dOTmZAO

 

@AlB Did I do something wrong? Smiley Sad

@Anonymous

Most likely nothing wrong Smiley Happy 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" }
    )
)

 

Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

You can add a MEASURE but i think it will repeat for each Column in the Matrix....

 

Measure =
CALCULATE ( SUM ( Table1[Amount] ), [Type] = "Available" )
    + CALCULATE ( SUM ( Table1[Amount] ), [Type] = "Requested" )

Another way could be to use Power Query

 

First pivot the "Type" Column...Then you can add the "Available" and "requested" Columns
Not unpivot again

 

Check this article as well
http://www.excelnaccess.com/adding-calculated-row-calculated-items-using-power-query/


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.