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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    johnbasha33
    Icon for Super User rankSuper User
    2 years ago

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

      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