Forum Discussion

apollo89's avatar
apollo89
Helper II
6 years ago
Solved

Use DAX Measure as Categories

Consider the following table:   Name Field Value A ABC -10 A DEF 15 B ABC -20 B DEF 25 C ABC 15 D ABC 20 D DEF 25 E ABC -1 E DEF 2   I am tryi...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    These calculated column formulas work

    Total for Name = =CALCULATE(SUM(Data[Value]),FILTER(data,Data[Name]=EARLIER(Data[Name])))
    Total for all names = SUM(Data[Value])
    Threshold = 0.05*[Total for all names]
    Category = if([Total for name]>[Threshold],Data[Name],"Others")

    Hope this helps.

amitchandak's avatar
amitchandak
Super User
6 years ago

apollo89 , Create this one as a new column

Category column= sumx(Table,Table[Value]) * 0.05

or

Category column= sumx(all(Table),Table[Value]) * 0.05

Then

Category Column = IF([Total Value] > [Category column],'Table'[Name],"Others")

  • apollo89's avatar
    apollo89
    Helper II
    6 years ago

    Thanks Amit.

     

    This was close but due to negative values also present in my dataset, the category columns are incorrect. Please see the revised dataset in my question. Apologies for the inconvenience.

      • apollo89's avatar
        apollo89
        Helper II
        6 years ago

        Thanks again Amit, however Categories as Measures won't work for me as I need to be place the Categories as Columns or Slicers in my visuals which is not possible with Measures.