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

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

Anonymous's avatar
Anonymous
Not applicable
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.