Struggling with Pareto-style Ranking/Analysis in PowerBI with Unified Data.

[Taurus1796]

Date	Category	ServiceID	Service	CustomerID	Revenue	Parent (lookup from Customer Master Table)
06/30/2025	Banking	111	Wire Transfer	C001	$1,500.00	Corp A
06/30/2025	Banking	111	Wire Transfer	C002	$3,700.00	Corp A
03/30/2025	Banking	222	Online Bill	C003	$1,200.00	Corp B
01/31/2025	Insurance	333	Claims	C004	$5,000.00	Corp C
01/31/2025	Insurance	333	Claims	C005	$4,000.00	Corp C
01/31/2025	Insurance	444	Premiums	C006	$1,800.00	Corp D

Hey everyone,

I’m working on a Pareto-style analysis in Power BI where I need to rank:

1. Services within each Category based on Fee Revenue (top 90%), and then
2. Customers within those top Services (again top 90%).

The data comes from multiple sources — a few Excel files and one DirectQuery SQL source — which I’ve flattened into a single unified fact table. I also have a lookup field in this table for customer rollups (e.g., grouping individual customer IDs under a parent/rollup name).

My challenge is:

• The ranking works fine when looking at services or customers in isolation.
• But when I bring everything together (Category → Service → Customer(Parent

	Flattened Unified Fact Table					
						
Date	Category	ServiceID	Service	CustomerID	 Revenue 	Parent (lookup from Customer Master Table)
06/30/2025	Banking	111	Wire Transfer	C001	 $1,500.00 	Corp A
06/30/2025	Banking	111	Wire Transfer	C002	 $3,700.00 	Corp A
03/30/2025	Banking	222	Online Bill	C003	 $1,200.00 	Corp B
01/31/2025	Insurance	333	Claims	C004	 $5,000.00 	Corp C
01/31/2025	Insurance	333	Claims	C005	 $4,000.00 	Corp C
01/31/2025	Insurance	444	Premiums	C006	 $1,800.00 	Corp D
						
						
						
	Customer master					
	CustomerID	RollupCustomer				
	C001	Corp A				
	C002	Corp A				
	C003	Corp B				
	C004	Corp C				
	C005	Corp C				
	C006	Corp D				
						

), the logic breaks, and the ranks or cumulative revenue % don’t behave as expected.

• I suspect it has something to do with how I’m using calculated columns or maybe context transition due to relationships/lookups or the use of ALL, ALLSELECTED, ALLEXCEPT.

Has anyone tackled a similar scenario?

Any best practices for handling multi-level Pareto-style breakdowns using a flattened table, especially when it involves lookups and mixed data sources?

Would appreciate any input, pointers, or examples.

Thanks in advance!


Desired output I would love is to have the date slicers work and visuals/matrix update.

	Desired Output		
	Date Slicer: June 2025		
			
	Category	Revenue	
	- Banking		
Top Service (s)	           -Wire Transfer  (80% of category)	 $4680 	=(1500+3700)*0.8
Top Customers within each Service (Rollup)	                    -Corp A (80% of service within category)	$3744	=(4680)*0.8
			
			
	Date Slicer: Jan 2025		
	- Insurance		
Top Service (s)	           -Claims (80% of category)	 $8640 	=(5000+4000+1800)*0.8
Top Customers within each Service (Rollup)	                    -Corp C (80% of service within category)	$6912	=(8640)*0.8

[v-agajavelly]

Hi @Taurus1796 ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Regards,
Akhil.

[v-agajavelly]

Hi @Taurus1796 ,

Just checking in to see if the solution worked out for you. Were you able to implement the multi-level Pareto breakdown as expected? If you ran into any issues or need help refining it for specific scenarios, feel free to share the details I’d be happy to assist further.

Regards,
Akhil.

[v-agajavelly]

Hi @Taurus1796 ,

Did you get a chance to try the approach I mentioned earlier for the multi-level Pareto breakdown? It should help with ranking services within each category, then drilling down to top customers while keeping your slicers and date context intact. Happy to know if it brought you closer to the result you were aiming for or if any edge cases are still popping up.

Regards,
Akhil.

[v-agajavelly]

Hi @Taurus1796 ,

Thanks for sharing such a detailed scenario really well explained. If you haven’t already, could you give @Nasif_Azam  approach a try? It directly tackles the multi-level Pareto breakdown you're aiming for first ranking services within each category based on revenue, then narrowing down to top customers (using the rollup logic) within those services.

The step-by-step DAX using RANKX, cumulative % logic, and handling filter context looks like it should work well with your unified fact table and slicers especially with the date context you mentioned. Would love to hear if that gets you closer to the desired outcome or if you're running into any edge cases.

Regards,
Akhil.

[Ashish_Mathur]

Hi,

The data you have pasted is not clear.  I sit possible to mock up some data in MS Excel and show the expected result there.  Write your Excel formulas/comments there, so that i can translate those formulas/logic into measures.

[Nasif_Azam]

Hey  @Taurus1796 ,

Your scenario is a classic multi-level Pareto analysis with filters at different hierarchies (Category → Service → Parent Customer), and it’s common to run into context transition issues when mixing ranking, cumulative percentages, and relationships. Try the following approaches:

1. Handle the Relationships Cleanly: Ensure that your flattened fact table has a relationship with:

• A Customer Master Table (for rollup) – Many-to-one, single direction.

• A Date Table – which is essential for the date slicer to work.

Use USERELATIONSHIP in measures if inactive.

2. Pareto Logic for Services (Top 80-90% in Each Category)

Create a measure to calculate total revenue per service within a selected category:

[Total Revenue] :=
SUM('FactTable'[Revenue])

Then calculate cumulative revenue % for services within each category:

[Service Rank within Category] :=
RANKX(
    FILTER(
        ALL('FactTable'[Service]),
        'FactTable'[Category] = MAX('FactTable'[Category])
    ),
    [Total Revenue],
    ,
    DESC
)

[Cumulative Revenue % by Service] :=
VAR TotalCategoryRevenue =
    CALCULATE([Total Revenue], ALL('FactTable'[Service]))
VAR CumulativeRevenue =
    CALCULATE(
        [Total Revenue],
        FILTER(
            ALL('FactTable'[Service]),
            [Service Rank within Category] <= MAXX(VALUES('FactTable'[Service]), [Service Rank within Category])
        )
    )
RETURN
DIVIDE(CumulativeRevenue, TotalCategoryRevenue)

3. Pareto Logic for Customers within the Top Services

After filtering to the top services, repeat the ranking for rollup customers within those services:

[Customer Rank within Service] :=
RANKX(
    FILTER(
        ALL('CustomerMaster'[RollupCustomer]),
        'FactTable'[Service] = MAX('FactTable'[Service])
    ),
    [Total Revenue],
    ,
    DESC
)

[Cumulative Revenue % by Customer] :=
VAR TotalServiceRevenue =
    CALCULATE([Total Revenue], ALL('CustomerMaster'[RollupCustomer]))
VAR CumulativeRevenue =
    CALCULATE(
        [Total Revenue],
        FILTER(
            ALL('CustomerMaster'[RollupCustomer]),
            [Customer Rank within Service] <= MAXX(VALUES('CustomerMaster'[RollupCustomer]), [Customer Rank within Service])
        )
    )
RETURN
DIVIDE(CumulativeRevenue, TotalServiceRevenue)

Then use this in a visual for top rollup customers (again filtered by <= 0.8).

Things to remember:

• Always use measures, not calculated columns, for ranking and cumulative % in Power BI — especially when slicers are involved.

• Use ALL to remove filter context when calculating total revenue per category or service.

• Use ALLEXCEPT carefully; sometimes ALL('Table'[Field]) within a FILTER block is more predictable.

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Taurus1796]

This gets close to solving it but fails. 

Services are ranked within Categories
Customers and Service ranking does not work together. 

Also, there is some syntax error in this dax. 

RANKX(
    FILTER(
        ALL('FactTable'[Service]),
        'FactTable'[Category] = MAX('FactTable'[Category])
    ),
    [Total Revenue],
    ,
    DESC
)

It works with  FILTER( ALL('FactTable'[Service], 'FactTable'[Category]),
                          'FactTable'[Category] = MAX('FactTable'[Category])


not with Filter(ALL('FactTable'[Service]) only. 



I ended up using a Python Script for now. It appears there is not solution to this in PowerBI environment.

[Nasif_Azam · Accepted solution]

Hey @Taurus1796 ,

Below is a Power BI–only pattern that:

(a) respects the date slicer,

(b) ranks Services within Category, then ranks Rollup Customers within the selected Service, and

(c) only shows rows that fall inside the Pareto threshold (80% in your examples). It avoids the ALL('Fact'[Service]) pitfall by working on virtual tables with ALLSELECTED (so slicers stay honored) and by scoping explicitly to the current Category/Service.

1) Base measure

Revenue = SUM ( Fact[Revenue] )

2) Service-level Pareto within Category

Rank Services in the current Category and compute cumulative % using a virtual table:

Service Rank (within Category) =
VAR vServices =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Fact ),
            Fact[Category], Fact[ServiceID], Fact[Service]
        ),
        "@Rev", CALCULATE ( [Revenue] )
    )
VAR vThisCat = MAX ( Fact[Category] )
RETURN
RANKX (
    FILTER ( vServices, [Category] = vThisCat ),
    [@Rev],
    [Revenue],
    DESC,
    DENSE
)

Service **bleep** % (within Category) =
VAR vServices =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Fact ),
            Fact[Category], Fact[ServiceID], Fact[Service]
        ),
        "@Rev", CALCULATE ( [Revenue] )
    )
VAR vThisCat = MAX ( Fact[Category] )
VAR vScope   = FILTER ( vServices, [Category] = vThisCat )
VAR n = RANKX ( vScope, [@Rev], [Revenue], DESC, DENSE )
VAR SumTopN = SUMX ( TOPN ( n, vScope, [@Rev], DESC ), [@Rev] )
VAR TotalCat = SUMX ( vScope, [@Rev] )
RETURN
DIVIDE ( SumTopN, TotalCat )

80% flag:

Service In Pareto 80% = IF ( [Service **bleep** % (within Category)] <= 0.8, 1, 0 )

3) Rollup-customer Pareto within the selected Service

Now rank RollupCustomer inside the current (Category, Service):

Customer Rank (within Service) =
VAR vRollups =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Fact ),
            Fact[Category], Fact[ServiceID], Customer[RollupCustomer]
        ),
        "@Rev", CALCULATE ( [Revenue] )
    )
VAR vCat  = MAX ( Fact[Category] )
VAR vServ = MAX ( Fact[ServiceID] )
VAR vScope = FILTER ( vRollups, [Category] = vCat && Fact[ServiceID] = vServ )
RETURN
RANKX ( vScope, [@Rev], [Revenue], DESC, DENSE )

Customer **bleep** % (within Service) =
VAR vRollups =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Fact ),
            Fact[Category], Fact[ServiceID], Customer[RollupCustomer]
        ),
        "@Rev", CALCULATE ( [Revenue] )
    )
VAR vCat  = MAX ( Fact[Category] )
VAR vServ = MAX ( Fact[ServiceID] )
VAR vScope = FILTER ( vRollups, [Category] = vCat && Fact[ServiceID] = vServ )
VAR n = RANKX ( vScope, [@Rev], [Revenue], DESC, DENSE )
VAR SumTopN = SUMX ( TOPN ( n, vScope, [@Rev], DESC ), [@Rev] )
VAR TotalServ = SUMX ( vScope, [@Rev] )
RETURN
DIVIDE ( SumTopN, TotalServ )

80% flag:

Customer In Pareto 80% = IF ( [Customer **bleep** % (within Service)] <= 0.8, 1, 0 )

4) A single measure to show only the Pareto rows at each level

Use ISINSCOPE so a single measure can behave differently at Category/Service/Rollup levels. It returns blank for rows outside the target 80% and Revenue for rows inside. This lets the same visual handle both steps.

Pareto Revenue (80-80) =
VAR AtService = ISINSCOPE ( Fact[ServiceID] ) && NOT ISINSCOPE ( Customer[RollupCustomer] )
VAR AtRollup  = ISINSCOPE ( Customer[RollupCustomer] )
RETURN
SWITCH (
    TRUE (),
    AtService && [Service **bleep** % (within Category)] > 0.8, BLANK (),
    AtRollup
        && ( [Service **bleep** % (within Category)] > 0.8
             || [Customer **bleep** % (within Service)] > 0.8 ),
        BLANK (),
    [Revenue]
)

Use SUMMARIZE with ALLSELECTED to build virtual tables instead of ALL(Fact[Service]) inside FILTER. Scope them back to the current Category/Service. Compute cumulative % using TOPN with rank, avoiding EARLIER issues. Combine ISINSCOPE with a single visibility measure so one visual can support both Service and Customer-level Pareto without filter conflicts.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Greg_Deckler]

@Taurus1796 Have a look at RankXDescMeasure3 and see if it gets you where you need to be: 

To *Bleep* with RANKX! - Microsoft Fabric Community

[Taurus1796]

Thanks. Thats helpful but it gets me halfway there to rank customers correctly within  Services. Services are not being ranked.