Cell Formatting

[sabd80]

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?

[Anonymous]

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.

[lbendlin]

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]

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