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.

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