Forum Discussion

apollo89's avatar
apollo89
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper II rankHelper 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
      Icon for Helper II rankHelper 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.