Different measures in Matrix visual

[Manish1198]

I have this requirement to show different measures in a matrix visual.

I put all the measures and in the Values and reduced the width of the column and created as below. The conditional formatting is the culprit creating disturbance. Is there any other way of doing it? Or is there a way to add the formatting only for specific columns? 

[MFelix · Accepted solution]

Hi @Manish1198 ,

For this you need to create a separate table with the category and the measure names:

The order column is to set the order how you want to have the measures.

Now add the following two measures:

Values = CALCULATE( SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
               1, [SalesForConsumer],
               2, SUM(Orders[Sales]),
               3, SUM(Orders[Profit]),
               4, SUM(Orders[Quantity])), 
         Orders[Category] in VALUES('Marix Visualization'[Category]))


Formatting = SWITCH(
    TRUE(),
    SELECTEDVALUE('Marix Visualization'[Order]) = 2 && [SalesForConsumer] < 5000 , 0,
    SELECTEDVALUE('Marix Visualization'[Order]) = 2 && [SalesForConsumer] >= 5000 , 1,
    SELECTEDVALUE('Marix Visualization'[Order]) = 4 && [SalesForConsumer] < 10 , 0,
    SELECTEDVALUE('Marix Visualization'[Order]) = 4 && [SalesForConsumer] >= 10 , 1)

Now create your matrix with the Category and Measure on the columns and place the Values measure on the measures.

Use the condittional formatting with a rule for the formmating measure:

See file attach.

[maruthisp]

Hi Manish1198,

I tried to implement a solution based on the original post description. I took some smaple data and tried to implement a solution. Please find the attached pbix file.

Different measures in Matrix visual.pbix

As per my knowledge, to show different metrics like Revenue, Cost, and Profit in a matrix, create separate DAX measures for each. Then, add them to the matrix and apply formatting only to the ones you want. This keeps your layout clean and avoids formatting issues.

Please let me know if there is any questions..

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[MFelix]

Hi @Manish1198 ,

What are the column you are hiding on the visual? 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Manish1198]

Hi @MFelix , @maruthisp 
Please find the detail explanation here. 
I want to show different measures under different categories and conditional formatting on sales and Quantity measures based on some rules as in this image. 
Ex:
SalesForConsumer, Sales, Profit for Furniture category
SalesForConsumer, Sales, Quantity for Office Supplies category
Sales, Profit, Quantity for Technology category. 

I am not sure if this is possible. 
I decreased the width of the not needed measures but the conditional formatting creates some disturbance. 
Here is the sample report
 Drive Link 

[MFelix · Accepted solution]

Hi @Manish1198 ,

For this you need to create a separate table with the category and the measure names:

The order column is to set the order how you want to have the measures.

Now add the following two measures:

Values = CALCULATE( SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
               1, [SalesForConsumer],
               2, SUM(Orders[Sales]),
               3, SUM(Orders[Profit]),
               4, SUM(Orders[Quantity])), 
         Orders[Category] in VALUES('Marix Visualization'[Category]))


Formatting = SWITCH(
    TRUE(),
    SELECTEDVALUE('Marix Visualization'[Order]) = 2 && [SalesForConsumer] < 5000 , 0,
    SELECTEDVALUE('Marix Visualization'[Order]) = 2 && [SalesForConsumer] >= 5000 , 1,
    SELECTEDVALUE('Marix Visualization'[Order]) = 4 && [SalesForConsumer] < 10 , 0,
    SELECTEDVALUE('Marix Visualization'[Order]) = 4 && [SalesForConsumer] >= 10 , 1)

Now create your matrix with the Category and Measure on the columns and place the Values measure on the measures.

Use the condittional formatting with a rule for the formmating measure:

See file attach.

[Manish1198]

Hi @MFelix 
Can we sort this based on some measure. 
Ex: Sort based on profit value from office supplies category

Appreciate your help.

Thanks
Manish

[Manish1198]

Awesome @MFelix Thanks for sharing your knowledge.

[Manish1198]

@MFelix does this by any chance work with calculated groups? This isn't working as expected when i use calculated groups in place of Orders[Category] . 

Values = CALCULATE( SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
               1, [SalesForConsumer],
               2, SUM(Orders[Sales]),
               3, SUM(Orders[Profit]),
               4, SUM(Orders[Quantity])), 
         'Calculation group'[Category] in VALUES('Marix Visualization'[Category]))

 

Please see the file File 
Thanks

[MFelix]

Hi @Manish1198 ,

Hi @Manish1198 ,

In this case you need to remove the calculate and filter part from the measure since you are forcing the filter trough the calculaiton group:

Values = 
        SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
            1, [SalesForConsumer],
            2, SUM(Orders[Sales]),
            3, SUM(Orders[Profit]),
            4, SUM(Orders[Quantity])
            )

[Manish1198]

@MFelix Do you mean all measures will be shown when we use calculated group? can't we show few measures in one category and other measures in other category?
Ex: Can we show the following when using Calculation group[Category]
Sales, Profit, Quantity in Office Supplies;
SalesforConsumer, Sales, Profit in Technology;
Sales, Profit, Quantity in Furniture category.

[MFelix · Accepted solution]

Hi @Manish1198 

Apologies you are correct do the following update to the measure:

Values = CALCULATE(
        SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
            1, [SalesForConsumer],
            2, SUM(Orders[Sales]),
            3, SUM(Orders[Profit]),
            4, SUM(Orders[Quantity])
            ), 'Marix Visualization'[Category] = SELECTEDVALUE('Calculation group'[Category]))

I also updated the matrix table acordingly.