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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

Pareto not working correctly

Hi all

 

I have the following table result

o59393_0-1660512759480.png

 

As seen the first row doesn't show the cumulative percentage, and the 4th row in the cumulative column is not summing correctly.

 

The DAX I have is:

 

 

Pareto UC Cumulative = 

VAR TotalUC = CALCULATE([Volume],ALLSELECTED('Ship From'))
VAR CurrentUC = [Volume]
VAR SummarizedTable = 
SUMMARIZE(
    ALLSELECTED('Ship From'),
    'Ship From'[Bottler Owner],
    "Volume", [Volume])
VAR CumSum = 
SUMX(
FILTER(SummarizedTable,[Volume]>=CurrentUC),
[Volume]
)
Return
DIVIDE(CumSum,TotalUC)

 

 

Any idea what could be wrong?

 

Thanks.

 

Here is the table:

 

Bottler OwnerVolumePareto UC CumulativePareto UC
1   480,600,034.00 37.01%
2   378,390,784.0066.14%29.14%
3   216,308,340.0082.80%16.66%
4   101,168,972.0082.80%7.79%
5     44,268,330.0094.00%3.41%
6     29,624,323.0096.28%2.28%
7     29,249,209.0098.53%2.25%
8     17,656,202.0099.89%1.36%
9        1,429,489.00100.00%0.11%
10 100.00% 
1 ACCEPTED SOLUTION
MahyarTF
Memorable Member
Memorable Member

Hi,

I tried to create a sample data for myself and then create measures on it, the result is below :

-----------------------------------

Pareto UC Cumulative =
Var Cumulative = IF(
                    min(Sheet99[Bottler Owner]) <= CALCULATE(max(Sheet99[Bottler Owner]), all(Sheet99)),
                    CALCULATE( sum(Sheet99[Volume]),
                            filter(all(Sheet99[Bottler Owner]),
                                    Sheet99[Bottler Owner] <= max((Sheet99[Bottler Owner]))  )
                            )
                    )
VAR CurrentUC = CALCULATE(sum( (Sheet99[Volume]) ))
Var SumAll = CALCULATE( sum(Sheet99[Volume]), all(Sheet99) )
Var Result = divide(Cumulative,SumAll)
Return Result
--------------------------------------------------
Pareto UC =
VAR CurrentUC = CALCULATE(sum( (Sheet99[Volume]) ))
Var SumAll = CALCULATE( sum(Sheet99[Volume]), all(Sheet99) )
Var Result = DIVIDE(CurrentUC, SumAll )
Return Result
MahyarTF_0-1660528295078.png
Mahyartf

View solution in original post

2 REPLIES 2
MahyarTF
Memorable Member
Memorable Member

Hi,

I tried to create a sample data for myself and then create measures on it, the result is below :

-----------------------------------

Pareto UC Cumulative =
Var Cumulative = IF(
                    min(Sheet99[Bottler Owner]) <= CALCULATE(max(Sheet99[Bottler Owner]), all(Sheet99)),
                    CALCULATE( sum(Sheet99[Volume]),
                            filter(all(Sheet99[Bottler Owner]),
                                    Sheet99[Bottler Owner] <= max((Sheet99[Bottler Owner]))  )
                            )
                    )
VAR CurrentUC = CALCULATE(sum( (Sheet99[Volume]) ))
Var SumAll = CALCULATE( sum(Sheet99[Volume]), all(Sheet99) )
Var Result = divide(Cumulative,SumAll)
Return Result
--------------------------------------------------
Pareto UC =
VAR CurrentUC = CALCULATE(sum( (Sheet99[Volume]) ))
Var SumAll = CALCULATE( sum(Sheet99[Volume]), all(Sheet99) )
Var Result = DIVIDE(CurrentUC, SumAll )
Return Result
MahyarTF_0-1660528295078.png
Mahyartf
Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.