Retain the same order when Import Excel Data to PBI Desktop and formatting

[PS_78]

Hello Team - I would like to show the data in the same sequence as I have the data entered in excel. I tried creating INDEX column and use it for sort. But as the same metric name repeats for every year, it doesn't work out. Please check the sample data below and let me know if it can be achieved.

Metric Name	Year	Target	Jan	Feb	Mar	Apr	May...
MN1	2023	10	8	9	10	12	9
MN3	2023	15%	16%	18%	22%	14%	12%
MN2	2023	0.5	0.45	0.475	0.5	0.55	0.4
MN1	2024	10	10	14	6
MN3	2024	15%	14%	20%	25%
MN2	2024	0.5	0.5	0.6	0.7

I want to create a table or matrix visual and show the metrics in the same sequence as shown in above table.

In addition, I also want to format the cells with different colors. If the value under a month is greater than or equal to target value then color it with green, else with red. I created a dynamic measure and handling the "%" symbol or $ symbol for amounts but how do I do the comparison and apply coloring.

Thanks,

Phani

[PS_78]

Thanks @v-zhouwen-msft for the response. I can follow the approach suggested for background color. But for sorting, I do not want to show Index column in the table / matrix visual. Also Metric, "MN3" is a %. I want to show it as 15%, 18% and so on not as 0.15, 0.18...

Thanks,

Phani

[v-zhouwen-msft]

Hi @johnbasha33 ,thank you for your participation, I'll add further.

Hi @PS_78 ,

If you don't want to show indexed columns, I can think of two ways to do it, but both have limitations.

1.'Sort by Column'.It requires column values that are unique, i.e., the 'Feb' column can be sorted according to the index columns created.' Mertic' column has duplicate values and cannot be sorted.

Sort one column by another column in Power BI - Power BI | Microsoft Learn

2.Add a graphic to cover the index columns

Regarding your display problem, refer to the following expression.

Target_1 = IF(SELECTEDVALUE('Table'[Metric]) = "MN3",
FORMAT(SELECTEDVALUE('Table'[Target]),"0%"),
SELECTEDVALUE('Table'[Target]))

[PS_78]

Thanks @v-zhouwen-msft . I actually created measures for individual metrics and using them in matrix visual in the sequence I need. This has helped with sequencing. But, I am facing challenges with coloring. I am trying to follow the approach you suggested for coloring, it works fine in table visual but in matrix visual, it doesn't work as expected. Giving below the screenshots.

However, if I select an orange row in table visual, then matrix will show in orange for that selected cell.

Can you please help me with this?

Thanks,

Phani

[v-zhouwen-msft]

Hi @PS_78 ,

The table data are as follows.

Please follow these steps:
1. Use the following DAX expression to create a column

Column = SUMX(FILTER('Tabelle1','Tabelle1'[Period Name] = "Target"),[Wtd Average Maturity])

2.Use the following DAX expression to create a measure

Measure = IF(SELECTEDVALUE(Tabelle1[Wtd Average Maturity]) > SELECTEDVALUE(Tabelle1[Column]),"Yellow",
IF(SELECTEDVALUE(Tabelle1[Wtd Average Maturity]) < SELECTEDVALUE(Tabelle1[Column]),"Orange"))

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PS_78]

Hello @v-zhouwen-msft  - Appreciate for all your kind help with this. I have replicated the issue in attached PBIX file. I don't see any option to attach the file. Hence gave google drive link. Can you refer the matrix visual created by me and help me out?

https://drive.google.com/drive/folders/1IUfvdik3Sa_g4C6cUjzGyjTAuZQiafky?usp=sharing

Thanks,

Phani

[v-zhouwen-msft]

Hi @PS_78 ,

The value of the 'Column' column is the determining condition, I found the value to be 2.74, the correct value should be 0.35, I modified the expression.

Column = SUMX(FILTER('Tabelle1','Tabelle1'[Period Name] = "Target" && 'Tabelle1'[Metric Name] = "Wtd"), Tabelle1[Value])

Modified the expression for the measure 'Measure'

Measure = IF(SELECTEDVALUE(Tabelle1[Metric Name]) = "CY %", BLANK(),
IF(SELECTEDVALUE(Tabelle1[Value]) > SELECTEDVALUE(Tabelle1[Column]),"Yellow",
IF(SELECTEDVALUE(Tabelle1[Value]) < SELECTEDVALUE(Tabelle1[Column]),"Orange")))

Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PS_78]

Hi @v-zhouwen-msft  - Thanks a lot. I can use this approach. I see that you restricted the target value in "Column" to only "Wtd". But I would have multiple measures (In the PBIX file I just took couple for example - for that instance I have to color code CY% cells too based on CY%'s target and actuals) and I have to color code each of them based on their targets and the actual values. I can still follow the suggested approach but I will have to create numerous columns for storing individual metrics "Target" and numerous measures for "Color Code".

I will wait for your response on this and "Accept your suggestion" as solution.

Thanks,

Phani

[v-zhouwen-msft · Accepted solution]

Hi @PS_78 ,

Please follow these steps:

1.Use the following DAX expression to create a table

Table = SUMMARIZE('Tabelle1',Tabelle1[Metric Name],"Target",CALCULATE(SUM(Tabelle1[Value]),'Tabelle1'[Period Name] = "Target"))

2.Creating table-to-table relationships

3.Use the following DAX expression to create a measure

Measure 2 = 
VAR _a = SUM('Table'[Target])
VAR _b = IF(SELECTEDVALUE(Tabelle1[Value]) > _a,"Yellow",
         IF(SELECTEDVALUE(Tabelle1[Value]) < _a,"Orange"))
         RETURN _b

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhouwen-msft]

Hi @PS_78 ,

Regarding your question, did you start out using the RANK function to create the indexed columns? Maybe you can use Power Query to create the index columns.

If you want to compare monthly data to a target value, use the following DAX expression.(January and February respectively, you can add the rest of the months by referring to this format.)

Measure = IF(SELECTEDVALUE('Table'[Jan]) >= SELECTEDVALUE('Table'[Target]),"Green","Red")

Measure 2 = IF(SELECTEDVALUE('Table'[Feb]) >= SELECTEDVALUE('Table'[Target]),"Green","Red")

 Setting the Conditional Format.

Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnbasha33]

@PS_78 
Create a new calculated column using DAX:
Index = 'YourTable'[Metric Name] & " - " & 'YourTable'[Year]

Conditional Formatting: To apply conditional formatting based on whether the value is greater than or equal to the target:

• Create a new measure to calculate whether the value meets the target:
  
  MeetTarget = IF('YourTable'[Value] >= 'YourTable'[Target], 1, 0)
  
  Use conditional formatting in your visual:
  • Go to the formatting options for the value field.
  • Choose "Data colors" or "Conditional formatting" (depending on your Power BI version).
  • Select "Advanced controls" or "Rules" to define the conditions.
  • Add a rule to format the color based on the MeetTarget measure:
    • If MeetTarget is 1, set the color to green.
    • If MeetTarget is 0, set the color to red.
      Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
  
  
  

[PS_78]

Hi @johnbasha33  - Thanks for the response. I understand about conditional formatting. But for sorting, how would creating an Index as concatenation of Metric Name and Year help? If I sort Metric Name based on the new Index column, it will not allow as the same Metric Name would have two different indexes. Remember, I want to show the metric names in the same sequence as entered in excel.

Thanks,

Phani