CALCULATE TOP 20% (Tableau Fixed Percentile to DAX)

[icos]

Hi all,

I've been trying to do this calculation and also see on previous posts, but couldn't find a solution that fits my needs.

I have on one Tableau file a Tableau LOD (Level of Detail) expression that is calculating whether each creditor's total spend is in the top 20% (80th percentile or above), and I would like to be able to replicate it on Power BI:

{FIXED [Creditors / Vendors], [ABC Class], [Segment Name] : SUM([Spend])} >= FIXED : PERCENTILE ( { FIXED [Creditors / Vendors], [ABC Class], [Segment Name] : SUM([Spend]), 80%) }

Output exepected on Power BI:
card visual with total spend value for Top 20% creditors
card visual with number of creditors that are on the Top 20%

The original dataset has 147,681 rows, and so when I am trying to apply some kind of running totals it returns "query resources exceeded" a lot of times.

I am sending a pbix file with the same structure as my real data, I've put the columns even more simple than in tableau, having only one date column, the creditor reference, and the spend.

Would really appreciate your help on this!! Thank you 

[v-pnaroju-msft · Accepted solution]

Thankyou, @VahidDM, for your response.

Hi icos,

We appreciate your question on the Microsoft Fabric Community Forum.

Based on my understanding of the scenario, please find attached a screenshot and a sample PBIX file that may assist in resolving the issue:

We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[VahidDM]

@icos 

Assume your table is Facts with columns:
Facts[Creditor], Facts[ABC Class], Facts[Segment], Facts[Spend]

1. Base measure
   Total Spend :=
   SUM(Facts[Spend])

2. Vendor totals (virtual table by current group)
   _vtVendorTotals :=
   VAR T =
   SUMMARIZE(
   FILTER(
   ALL(Facts),
   Facts[ABC Class] IN VALUES(Facts[ABC Class]) &&
   Facts[Segment] IN VALUES(Facts[Segment])
   ),
   Facts[Creditor],
   "Tot", CALCULATE([Total Spend])
   )
   RETURN T

3. 80th percentile threshold (per ABC Class × Segment)
   Top20 Threshold :=
   VAR T = [_vtVendorTotals]
   RETURN
   PERCENTILEX.INC(T, [Tot], 0.8)

4. Card: total spend of top-20% creditors (by current ABC Class × Segment filter)
   Top20 Spend :=
   VAR T = [_vtVendorTotals]
   VAR Th = [Top20 Threshold]
   RETURN
   SUMX( FILTER(T, [Tot] >= Th), [Tot] )

5. Card: number of creditors in top-20%
   Top20 Creditor Count :=
   VAR T = [_vtVendorTotals]
   VAR Th = [Top20 Threshold]
   RETURN
   COUNTROWS( FILTER(T, [Tot] >= Th) )

Notes
• Slice by ABC Class and Segment (or keep them in the visual/page filter) so the percentile is computed within each group.
• These are measures (no calculated tables/columns), so they should be memory-friendly even on large row counts.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[icos]

Hello @VahidDM , thank you very much for your reply!

And how could I be sure that this is also dynamically changing based on the year/month selected? The fact table also has a date column with the format YYYY-MM-01

To make it more simple, we can also ignore the segment and ABC column, assuming the fact table has the following columns: date, creditor, spend