Forum Discussion

EpicTriffid's avatar
EpicTriffid
Helper IV
6 years ago

Changing number format on single visualisation

Hi there,

 

I have two fields: Actuals and Targets. These are related to different KPIs. For most, the KPi should be in %, but for one, International Enrolments (as well as a few others), it should be whole numbers. 

 

You can see above that the line chart is showing % as that is how Actuals and Targets has been formatted. 

In the tables above I got around this problem by creating measures to format them as text in specific ways:

SwitchScores = SWITCH([SelectedKPI],
"INT_ENR_YR_START",FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0"),
"INT_ENR_YR_END",FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0"),
"AWARDS", FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0,,.00"),
"CITATIONS", FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0.00"),
"ENV_SUS", FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0.00"),
"RKE_INCOME", FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0,,.00"), 
FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]),"0%"))

 

I've read up about it and found I can't use measures as the value field in a line graph, so I've tried using the same code to create a new column, when I load it into the visualization, I don't get any values. I tried to get simpiler by just using:

Column = FORMAT(SUM(KPIs_COMBINED_RESHAPED[Actuals]), "0")

But that also didn't work. You can see below how it looks:

 Am I missing something here?

Need Help
Tips and Tricks

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi EpicTriffid ,

     

    As far as i know, Format() returns a test instead of a value so that we couldn’t add returned result to values while we are using Line Chart.

    Please check followings steps as below:

    1. Create calculated column:

         act = SWITCH(Table1[KPIS],"A",Table1[actuals]/100,"B",Table1[actuals])

         tar = SWITCH(Table1[KPIS],"A",Table1[targets]/100,"B",Table1[targets])

    2. change format for these two columns:

    3. results would be shown as below:

    Pbix as attached, hopefully works for you.

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.