Forum Discussion

corange's avatar
corange
Post Patron
6 years ago
Solved

Formatting based on condition DAX

Hi Team, 

 

I have played with my data set so I could manipulate the Matrix view a bit more. 

 

I have unpivot a table using two columns so now i have got : 

 

Column "Category" where I have job count and revenue as attribute 

Column "Value" where I have got the count of jobs and revenue falling under one but easily differentiated due to column "category" 

 

It works like a treat and I can select if I want to see all or a specific attribute now. The only thing is that revenue should be fornatted as $ but not job count. Is there a formula for me to use that will allow based column Category to format my value on conditions and leaving the value numerical? 

 

Thanks. 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Seems like it should be along the lines of:

     

    Column = IF([Attribute] = "REVENUE",CONVERT([Value,CURRENCY),[Value])

12 Replies

    • corange's avatar
      corange
      Post Patron
      6 years ago

      Do you guys know what we will be the trick to add this as a column instead of a measure? 

       

      TEST = IF(SELECTEDVALUE(SummaryOpsBI[Attribute])="Revenue",FORMAT(CALCULATE(SUM(SummaryOpsBI[Value])),"Currency"), FORMAT(CALCULATE(SUM(SummaryOpsBI[Value])),""))
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        FORMAT is going to make those values text, I thought you wanted to keep them numeric?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    Ooo, been round and round on this one. Last time I went down that route it was a hard no.
    • corange's avatar
      corange
      Post Patron
      6 years ago

      Hi Greg_Deckler , 

       

      i have found this one and it does work. However, I dont know how to make it respond to the other filter I have got in slicer. For example when I select data range March to April and drop this measure in, it will expand and show me for the entire FY I have got in store. 

       

      Any idea?