Forum Discussion

magnifybi's avatar
magnifybi
Resolver I
6 years ago

How to format my SWITCH Measure as Currency and integer depending on filter

 
Measure Selection =
SWITCH( TRUE(),
VALUES('Measure Dimensions'[Measure]) = "Total Sales", Transactions[Sales Value Total],
VALUES('Measure Dimensions'[Measure]) = "Total Quantity", Transactions[Quantity Total],
VALUES('Measure Dimensions'[Measure]) = "Total Transactions", Transactions[Transaction Count],
BLANK())
 
I have a measure that works off a filter to bring back sales, quantity or transaction count, what I want is the sales to be formatted as $ and the other two to be integer 
 
Any ideas how to solve this one?
Need Help

11 Replies

    • magnifybi's avatar
      magnifybi
      Resolver I
      6 years ago

      Hey, thanks for the reply, I don't see how that thread helps with my formatting of the results, have I missed something?

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi magnifybi ,

         

        Sorry, based on the formula you gave, I could not quite know your scenario.

        Please try to format the columns involved in the formula to the data type you want.

        If I mistake, you can paste some screenshots, or share the sample pbix via cloud service like onedrive for business.

        Please mask any sensitive data before uploading


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

  • PowerUnilam's avatar
    PowerUnilam
    New Member
    4 years ago

    I Totally understand you, I have the same issue. I wrote sth like this, but the problem is that it is a string format, so that it doesnt order the column descending or ascending properly. Have you find a better way? Thanks

     

    Total Seleccion =

    IF(ISCROSSFILTERED(Selector[Seleccion]),

    SWITCH(TRUE(),
    VALUES(Selector[Seleccion]) = "Ventas $", Format(Ventas[Ventas $],"Currency"),
    VALUES(Selector[Seleccion]) = "Cantidad", [Cantidad SKU Vendida],
    Ventas[Ventas $]),
    Ventas[Ventas $])
  • emilmorkeberg's avatar
    emilmorkeberg
    Frequent Visitor
    4 years ago

    From my understanding what you want to do is having the Total Quantity and Total Transactions as whole numbers and Total Sales as $. You can do that in the Switch by using the formula below, however the output will be a string, thus you cant sort it AFAIK.

     

     

    Measure Selection =
SWITCH( TRUE(),
VALUES('Measure Dimensions'[Measure]) = "Total Sales", FORMAT(Transactions[Sales Value Total],"Currency"),
VALUES('Measure Dimensions'[Measure]) = "Total Quantity", Transactions[Quantity Total],
VALUES('Measure Dimensions'[Measure]) = "Total Transactions", Transactions[Transaction Count], BLANK())

     

     

    You could also just use 3 bookmarks (by creating 3 buttons) instead of the switch and by that format the Total Sales measure as $ and the two others and whole numbers.

     

    EDIT: Just saw this thread was made in 2020... 🙂

    • JMG241188's avatar
      JMG241188
      Helper I
      1 year ago

      Hey,

       

      I don't think I did, unfortunately, I believe I just left it as integer numbers.

       

      I will try and look back over my model later today and see if I came up with an solution.

       

      Sorry I couldn't be of more help.

       

      Thanks,

       

      Jon