Forum Discussion
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 trying to create categories for 'Name' when the aggregate value of each 'Name' is more than 0.05 times the total of all categories.
For example, Total Value of all is 71 and 71 * 0.05 = 3.55.
So when Total Value of each 'Name' is greater than 3.55, mark it as the 'Name' otherwise mark it as 'Others'.
End result:
| Name | Total Value | Category |
| A | 5 | A |
| B | 5 | B |
| C | 15 | C |
| D | 45 | D |
| E | 1 | Other |
This category needs to be column as I will need to place it as a Column in visuals.
Steps Taken:
1) I build a measure to get aggregated values of 'Name' multipled by 0.05
2) Trying to create a Column of this:
Which is not correct.
Any help will be appreciated.
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.
16 Replies
Hi,
Write these measures
Total value = SUM(Data[Value])Threshold = 0.05*CALCULATE([Total value],ALL(Data[Name]))Measure = if(HASONEVALUE(Data[Name]),if([Total value]>=[Threshold],VALUES(Data[Name]),"Others"))Hope this helps.
Thanks Ashish.
However I need to be placing 'Measure' as a column or a slicer in my report visuals which is not possible since it's a Measure. Any other suggestions?
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.
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
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")
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.
Here you have 2 versions: one uses an absolute calculation and another one that uses a relative calculation. You don't have to create columns in any tables to make it work. All you need is a disconnected table and a few measures. This is a very flexible design that I think you'll appreciate when you see it.
Best
D
Thank you amitchandak and Ashish_Mathur ! I understand the EARLIER function was vital to this. Thank you both for your efforts!
You are welcome.
Is there anyway to get totals by Category (relative or absolute) i.e. not requireing Name to be part of the Table visual?
For example in Relative method to select Category[Category Name] in the filter and to see
"Others, 7"
in the table view
I.e. using a measure (that doesn't appear as a data element in the Input table)Hi,
Share some data, describe the question and show the expected result.
https://1drv.ms/u/s!Am26UCUW42k4gd1lNr7zuQ2pCdEBlQ?e=gm0fF8
basically I want to show summary totals based on the status.
the status is dynamic based on the dates chosen.
so when the dates are set to show a combination of statuses
say 2 on active-full and two on active-partial
I want to be able to show on a separate cards (or table) the totals for both statuses and have that dynamically change when the dates change. So if dates change to make all transactions have a status of Future then we should have a total for just that status.
hope I'm making sense (and hope the quickly put together sample helps.
