Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
apollo89
Helper II
Helper II

Use DAX Measure as Categories

Consider the following table:

 

NameFieldValue
AABC-10
ADEF15
BABC-20
BDEF25
CABC15
DABC20
DDEF25
EABC-1
EDEF2

 

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:

 

NameTotal ValueCategory
A5A
B5B
C15C
D45D
E1Other

 

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

 

Category Measure = CALCULATE([AMount] * 0.05,ALL('Table'[Name]))
 
This needs to ignore any filters for 'Name' but needs to adhere to any other filter/slicer like 'Field'
 
PBI1.PNG
 

2) Trying to create a Column of this:

 

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

PBI2.PNG

Which is not correct.

 

Any help will be appreciated.

2 ACCEPTED SOLUTIONS

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
barryc
Regular Visitor

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

apollo89
Helper II
Helper II

Thank you @amitchandak and @Ashish_Mathur ! I understand the EARLIER function was vital to this. Thank you both for your efforts!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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.

@apollo89 ,

Please find the attached solution after signature

 

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.

@apollo89 , Check new category column

Anonymous
Not applicable

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!

Greg_Deckler
Super User
Super User

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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.