Matrix visual

[johnbasha33]

this visual is a matrix in Power BI, Rows are coming from Division column column values are coming from Bucket. this bucket has a single blank value thats why it is blank before paste due bucket. and finally we have values coming from On hand qty, Current Value, and open purchases. since the bucket has one blank, values are being showed like that my expectation is.. I'm trying to create a table where only On Hand Qty and Current Value show up for the blank bucket. Past Due, Aug'23, Sep'23, etc; all of these buckets should only have Open Purchases. I.e. remove the empty columns. Do you know how to achieve this?

is it possible to acheive like below in Power BI matrix?

@amitchandak @Idrissshatila @Ritaf1983 @parry2k @Ahmedx @Pragati11 @mwegener @Greg_Deckler @lbendlin @Syk @danextian @Ashish_Mathur @MFelix 

[johnbasha33]

@mwegener expanding hierarchy works but we have other rows not looking good.

The downside is the table looks bad because there's part numbers with inventory, but no purchases.

[mwegener]

How did you deal with this problem before?

[PaulDBrown]

See if this helps

https://community.fabric.microsoft.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-Power... 

[johnbasha33]

@mwegener  this is working for one measure at a time, is there a way to show up all 3 at a time? 

[mwegener]

Have you expanded the hierarchy in the columns?

In the attachment you can find my example file

[johnbasha33]

@mwegener @lbendlin @Ritaf1983 @amitchandak @Pragati11  can someone please assist with this?

[mwegener]

Hi,

Power BI only hides categorical columns no measure columns.

So you need a disconnected table with a column containing the identifiers "On Hand Qty", "Current Value" and "Open Purchases".

Using a measure and the SWITCH statement, you then assign the calculation of the corresponding key figure to each column label.

Sample code for the measure:

SWITCH (
    SELECTEDVALUE(ColumLayout[Colum]),
    "On Hand Qty", [On Hand Qty],
    "Current Value", [Current Value],
    "Open Purchases", [Open Purchases]
)

Best regards
Marcus

[johnbasha33]

thanks for looking into this, dint exactly followed your suggestion.

you want me to create a separate table only with these 3 columns values in one column and use the above switch ,easure in the matrix?

[mwegener]

Yes, because only column values are suppressed if they are blank.

[johnbasha33]

how to do that, can you give me any reference?