Forum Discussion

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

DAX Commands

I want to submit below DAX commands as my all time favourite along with sample pbix file and how I've used them numerous times in my reports and dashboards and have worked without glitch.   HASONEV...
AlexNYExcel's avatar
AlexNYExcel
Advocate II
10 years ago

Very useful formula is to calculate measure for any group in Dimension Column.

Example, you have table with Customers and columns ID, Customer Name, Customer Industry, etc.

The question you might have is to group Clients by their contribution to the total of Industry.

According to Pareto rule (80/20) there will be some very large contributors and bunch of small one.

% contribution = SUM(fact_table[column])/CALCULATE(SUM(fact_table[column]),FILTER(table, table[column]=EARLIER(table[column])))*100

Contributors Groups = IF(table[% contribution]>90, "1. 90%+", 
IF(table[% contribution]>75, "2. 75%+", 
IF(table[% contribution]>60, "3. 60%+", 
IF(table[% contribution]>50, "4. 50%+", 
IF(table[% contribution]>40, "5. 40%+", "6. other")))))

 

 

It could be used for grouping anything, for example for Client you might have multiple locations and unique ID for each location, but single NAME - so you could calculate MEASURE and assign it to some sort of Group/Bracket that you could move to slicers.

 

Let me know if it was useful!

Alex