How to colour each cell on the matrix based on a calculation

[Osamurai3]

Hello,

I am trying to reproduce the following graph in Power BI, but I cannot get it to work, can anyone help me?

The operations I would like to perform is as follow.

• If the Ratio value in the Budget row is above 0, the six cells in that group are coloured.
  • For example, for Product_A in Area_A, the Ratio is 3.33% (cell D4), so cells C3~D5 are painted blue.
  • For Product_A in Area_B, the Ratio is -20% (cell D7), so it is not painted blue but remains white.
  • Confusingly, the Ratio in the Last Month row is not used - look at Product_A in Area_D. The Budget Ratio is 4.76% (cell D13), but the Last Month Ratio is -8.33% (cell D14). In this case, C12~D14 are coloured blue because D13 is above 0.

I would like to set the data tables as follows.

I would like to ask additionally, can the Ratio column of this table be done within Power BI?

Currently I do the calculations in Excel, but it would be nice if I could do it in Power BI.

Appreciated your help,

[Osamurai3]

Hi Ibendlin,

Thank you for your advice.

I was finally able to get the results I wanted.

The more basic part was wrong.

The file I uploaded yesterday did not have the background color turned on in the matrix visual settings.

Thanks for the information you've given.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Osamurai3]

Hi Ibendlin,

Thank you for your reply.

I uploaded file to the dropbox below,

https://www.dropbox.com/scl/fi/7u5sh565952sx8blgb8gu/Sample.xlsx?dl=0&rlkey=nmwj9ehgvv0ti2aoj2hhbo1t...

The first sheet is row data and second sheet is outcome.

Thanks,

[lbendlin · Accepted solution]

Thank you for the sampe data. See my proposal attached.

[Osamurai3]

Hi Ibendlin,

Thank you for your kind help, I saw your attachment and have tried to reproduce it in my Power BI.

However, since I was not able to reproduce it completely, could you point out the areas where it was not done?

Here is the reproduced filed.

https://www.dropbox.com/s/290jt0yyhzmphpp/Sample.pbix?dl=0

what I could

• I was able to write the "bgcolor" expression with the DAX function.

what I couldn't

• Cells are not turning blue.
• "Batch" in the data sheet is not used to create the matrix
• The "bs" column of the data sheet has not been created.

The following is my guess.

• Since the "bgcolor" measure has been created, is there any other setting that needs to be made?
• I seem to be creating relationships in a different way with you, does this affect this error? I describe my method of doing this for reference. There might be some differences in terminology because I am doing this in the Japanese version of Power BI.
  1. Duplicate the original data.
  2. Delete unwanted columns.
  3. Remove duplicates in the Batch column.
  4. Create an index column from Add Column.

Thanks a lot,

[lbendlin]

See attached.  For some reason the batch sort still works despite the absence of the bs column. If the sort breaks you will have to add that back in.