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 . 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

        Hi Anonymous  - 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