Forum Discussion

sabd80's avatar
sabd80
Helper IV
2 years ago

Cell Formatting

Hi All,

Some of the users are connecting to the Power BI semantic model from excel.
They need to see specific figures in a certain order, so I created below measure:

Actual Testing =
SWITCH([Row Group],
    10, [Cases Actual],
    20, [Tonnes Actual],
    130, [Contribution Actual],
    150, [Trade spend % (GSV) Actual],
    170, [Contribution % (NSV) Actual],
    180, [GSV / Tonne Actual]
)
 
When they connect to the model from excel they see below, but they want to see the values formatted as in the green highlighted column:


As you can see each line represent different scale, so I can't format the measure in Power BI.


I understand there are 2 options to achieve the desired result:
1. Do the formatting in excel, this is not acceptable by the users

2. formatt the measures in Power BI as below, but the values will show in excel as texts, which also not acceptable:

Actual Testing formatted =
SWITCH([Row Group],
    10, FORMAT([Cases Actual], "#,###"),
    20, FORMAT([Tonnes Actual], "#,###"),
    130, FORMAT([Contribution Actual],"#,###"),
    150, FORMAT([Trade spend % (GSV) Actual], "#,##0.0%"),
    170, FORMAT([Contribution % (NSV) Actual], "#,##0.0%"),
    180, FORMAT([GSV / Tonne Actual], "#,##0.00")
)
is there away to achieve this?

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    All values in a column must have the same data type. 

     

    What you can try instead is use separate measures (and "show values on rows")  and then export to Excel with formatting.

    • sabd80's avatar
      sabd80
      Helper IV
      2 years ago

      thanks lbendlin , the measure I shared is just a sample, the list in the acutal one is much bigger, and we have to replicate it for Bugdet, and 12 month forecast, so it will make the list of measures so big and confusing

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, sabd80 

    May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.


    If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.