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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## How to filter out the tables based on the Top 50% Contribution of Total Sales?

Hi All,

Consider the below table where we have Product Category & Product Subcategory and the corresponding Total Sales.

We need get only the Product Category & sub category which contributes the Top 50% of the Total Sales.

Step 1 - Calculate the Contribution % for each row. (i.e) = 167 / 656(Total Sales)
Step 2 - Calculate Cumulative sum of Contribution %

Step 3 - From Step 2, get only the records which is in the range of 0% <= x <= 50%

In the below example, only first two records will come. So the table visual should filter accordingly and show only first 2 records.

 Product Category Product Sub Category Total Sales Contribution % Cumulative Sum % Rank A A1 167 25.46% (=167/656) 0% 1 A A2 150 22.87% (=150/656) 48.32% (=25.46%+22.87%) 2 A A3 139 21.19% (=139/656) 69.51% (=48.32%+21.19%) 3 A A4 110 16.77% (=110/656) 86.28% (=69.51+16.77%) 4 A A5 90 13.72% (=90/656) 100.00% (=86.28%+13.72%) 5 Total = 656 (=167+150+139+110+90)

Please help me in achieving the above logic. Thanks in advance !!

3 REPLIES 3
Resolver III

Hello @MadhumithaV_26 You can use SUMX to avoid any error Try out the below DAX
Calculated Columns :-

i.e

Contribution % =
VAR _x = SUMX('XOX',[Total Sales])
VAR _y = [Total Sales]
RETURN _y / _x
And
Cumulative Sum % =
VAR _res =
CALCULATE(
SUM('XOX'[Contribution %]),
FILTER(
ALL('XOX'),
'XOX'[Contribution %] >= EARLIER('XOX'[Contribution %])
)
)
RETURN
IF(_res = 'XOX'[Contribution %],0,_res)
Resolver III

Hello @MadhumithaV_26 This might be what you need

Frequent Visitor

This is not working for me. Total Sales is a measure in my use case. So I can't able to use sum or Earlier function on top of a measure. Can you help me solving this?

Thanks

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors