Forum Discussion
Helper IIUse DAX Measure as Categories
apollo89 , Check new category column
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.
Helper IIThanks 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.
Super User- apollo89
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.
- amitchandak
apollo89 , Check new category column
Hi,
I have data with customer ids where each row represents transaction info. I am supposed to find average transaction per day for each customer. There is a slicer for date where I can choose last 30 days, or last 7 days. According to the chosen dates, avg per day gets changed. If I just use existing table, there exists an issue of correct average. For example, a customer did 10 transactions in only 5 days among our chosen 30 days. We get average here 10/5 instead of 10/30. The only way to solve this is by creating a measure using another table called calender, having one to many relationship with date of existing table. It solves the problem.
Now, I need to make a pie chart with categories like less than 0.5 transaction per day on avg, less than 1, less than 2, more than 5 etc. How can I create such categories using just the measure "avg per day". As we can not make a column in the table as avg per day because the avg values in the table doesnt change with slicer date (as discussed before eg, last 30 days or last 7 days). I could not figure out how to make categories in pie chart using just a measure here. Kindly help me in this!