Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
PS_78
Helper II
Helper II

Retain 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 NameYearTargetJanFebMarAprMay...
MN12023108910129
MN3202315%16%18%22%14%12%
MN220230.50.450.4750.50.550.4
MN120241010146  
MN3202415%14%20%25%  
MN220240.50.50.60.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

1 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"))

vzhouwenmsft_0-1713342236991.png

2.Creating table-to-table relationships

vzhouwenmsft_1-1713342331237.png

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

vzhouwenmsft_2-1713342418820.png


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.

View solution in original post

11 REPLIES 11
PS_78
Helper II
Helper II

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.

vzhouwenmsft_0-1712907591015.png

vzhouwenmsft_1-1712907666001.png

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

2.Add a graphic to cover the index columns

vzhouwenmsft_2-1712907876375.png

vzhouwenmsft_3-1712907900626.png

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.

PS_78_0-1712963897358.png

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

PS_78_1-1712963971645.png

Can you please help me with this?

Thanks,

Phani

 

Hi @PS_78 ,

The table data are as follows.

vzhouwenmsft_0-1713169716875.png

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

vzhouwenmsft_1-1713169895624.png

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

vzhouwenmsft_0-1713251885372.png


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"))

vzhouwenmsft_0-1713342236991.png

2.Creating table-to-table relationships

vzhouwenmsft_1-1713342331237.png

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

vzhouwenmsft_2-1713342418820.png


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
Community Support
Community Support

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.

vzhouwenmsft_0-1712808918850.png

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.

vzhouwenmsft_1-1712809186463.png

vzhouwenmsft_2-1712809216874.png

vzhouwenmsft_3-1712809262624.png

Final output

vzhouwenmsft_4-1712809296147.png

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:

  • 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 !!




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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.