Forum Discussion
Helper IIRetain the same order when Import Excel Data to PBI Desktop and formatting
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
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 _b4.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.
11 Replies
PS_78Thanks Anonymous 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
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 Anonymous . 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
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
Super User
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
- Create a new measure to calculate whether the value meets the target:
