Forum Discussion

PS_78's avatar
PS_78
Icon for Helper II rankHelper II
2 years ago
Solved

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 y...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

PS_78's avatar
PS_78
Icon for Helper II rankHelper II
2 years ago

Thanks 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PS_78
      Icon for Helper II rankHelper II
      2 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.