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 ,

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

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