Let’s use DAX to see if transaction data adheres to the 80-20 rule.

80-20 Rule

Concept is that 80% of outcomes result from 20% of all causes for an event. Often used to identify the most productive inputs and prioritize them.

Requirement

Some products have more sales than others. I want to see how many of the 18 products are needed to reach 80% of total sales.

File: Pbix

Set-up

Product table has 18 Product Codes. Normally a dimension table like this would have more columns describing each product. For simplicity I have 1.

Table Sales has 90 rows (transactions). 5 for each product.

I added a 1 to many relationship between Product and Sales.

Solution

4 calculated columns to determine the products needed to reach 80% of sales.

Final visual shows us only 6 (33.33%) products produce bulk of sales.

80 20 (step1) Product Sales

I calculate sales for each individual product. CALCULATE([Total Sales]) works but CALCULATE with ALLEXCEPT (definition) is more specific. “//” indicates a comment.

80 20 (step1) Product Sales = 
//CALCULATE([Total Sales])
CALCULATE([Total Sales],ALLEXCEPT('Product','Product'[Product Code]))

Step 1 shows each product’s sales. Normally product sales amount is calculated using a SUM measure and then used in a visual but I need these amounts visible in a table.

80 20 (step2) Product Sales RT

RT = running total (aka expanding/cumulative). Slow on a transaction table like Sales but a Product table is smaller (each product is listed only once).

The variable gets the current product’s sales. FILTER creates a table with sales >= the variable. SUMX sums qualifying amounts from FILTER.

80 20 (step2) Product Sales RT = 
VAR CurrentRowAmount = 'Product'[80 20 (step1) Product Sales]
RETURN
SUMX(
     FILTER(
            'Product',
            'Product'[80 20 (step1) Product Sales] >=CurrentRowAmount
     ),
     'Product'[80 20 (step1) Product Sales]
)

80 20 (step3) RT%

Current row’s running total as percent of total sales. CALCULATE with ALL gives us grand total sales amount.

80 20 (step3) RT% = 
DIVIDE('Product'[80 20 (step2) Product Sales RT],
       CALCULATE([Total Sales],ALL())
)

80 20 (step4) Group

Labels rows ‘Product Top 80%’ or ‘Product Other 20%’. Used in final visual.

80 20 (step4) Group = 
IF('Product'[80 20 (step3) RT%]<=0.8,
   "Product Top 80%",
   "Product Other 20%")

Bonus Solution

File: pbix

Requirement

How can I modify 80-20 to 85-15 and also have a dynamic description?

Make Target Amt Visible

I added this calculated column. Now target amount is visible in a column (not hidden inside the DAX as in original solution).

80 20 (step4) Group = 0.85

Change it to desired decimal value (0.85 = 85-15). It’s used in step 5 below to build the text description (also used in measure ‘Products Top X % Label’).

80 20 (step5) Group = 
VAR topX = 'Product'[80 20 (step4) Group]
VAR bottomX = 1-topX
RETURN
IF('Product'[80 20 (step3) RT%]<=topX,
   "Product Top "&FORMAT(topx, "##,#%"),
   "Product Other "&FORMAT(bottomX, "##,#%"))

Top X Product Codes Viz

Measure ‘Products Top X %‘ concatenates Product Codes. Measure ‘Products Top X % Label’ is a dynamic Title (select visual, Format, Title).

Products Top X % =
CONCATENATEX (
    FILTER (
        'Product',
        LEFT ( 'Product'[80 20 (step5) Group], 11 ) = "Product Top"
    ),
    'Product'[Product Code],
    ", "
)

Products Top X % Label = 
VAR Xamount =
 MIN('Product'[80 20 (step4) Group])
VAR countprodtop =
 COUNTROWS(FILTER('Product',LEFT('Product'[80 20 (step5) Group],11)="Product Top"))
VAR texttop = 
 countprodtop & " products required to reach "&FORMAT(Xamount,"##,#%")&" of sales"
RETURN
texttop

Below the same measure with comments, additional variable, line breaks. It’s longer but easier to read.

Products Top X % Label = 
//harvest target amount and format it
VAR Xamt = MIN('Product'[80 20 (step4) Group])
VAR Xamtf = FORMAT(Xamt,"##,#%")

//count products in the top X group.
VAR counttopX =
 COUNTROWS(
     FILTER('Product',LEFT('Product'[80 20 (step5) Group],9)="Product T"))

//build the final text used in dynamic title
VAR texttop = 
  counttopX & " products required to reach " & Xamtf & " of sales"

RETURN
texttop

Sample Data

I explored 4 ways to create the data for table Sales here. Basic data-set but good for focusing on the concept.

Summary

Calculated Columns

In a fact table (Sales) they aren’t a good idea (fact tables can be very large). In this post they are in a dimension table (Product) that is much smaller.

Bonus Solution

I explored how to harvest the target amount and use it in visuals as a dynamic title and dynamic row text (not possible in my original solution as the 0.8 amount was inside calculated column DAX).

Measure Solution?

Can I create a solution using less (no?) calculated columns and more measures? Is it possible?