Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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
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]))
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
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.
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
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.
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
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.
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.
@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:
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |