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
Anonymous
Not applicable

% Running total in vendor DAX

Hi

 

I am trying to make a pareto chart in Power BI Desktop

I'm almost there, but cant figure out how to create the % running total 

The example below is from Excel

 

The count of cases is in place, i just need the running total in precentage

 

;support45_total.PNG

1 ACCEPTED SOLUTION

@Anonymous

 

Just made an update to PBI.

 

ImgR.png

 

And the measure look like this:

 

% Running =
VAR Ranking =
    RANKX (
        ALLSELECTED ( Table1[Vendor] ),
        RANKX ( ALLSELECTED ( Table1[Vendor] ), CALCULATE ( COUNT ( Table1[Cases] ) ) )
            + DIVIDE (
                RANKX (
                    ALLSELECTED ( Table1[Vendor] ),
                    CALCULATE ( VALUES ( Table1[Vendor] ) ),
                    ,
                    ASC
                ),
                COUNTROWS ( ALLSELECTED ( Table1[Vendor] ) ) + 1
            )
    )
VAR Acumulada =
    CALCULATE (
        COUNT ( Table1[Cases] ),
        FILTER (
            ALLSELECTED ( Table1[Vendor] ),
            RANKX (
                ALLSELECTED ( Table1[Vendor] ),
                RANKX ( ALLSELECTED ( Table1[Vendor] ), CALCULATE ( COUNT ( Table1[Cases] ) ) )
                    + DIVIDE (
                        RANKX (
                            ALLSELECTED ( Table1[Vendor] ),
                            CALCULATE ( VALUES ( Table1[Vendor] ) ),
                            ,
                            ASC
                        ),
                        COUNTROWS ( ALLSELECTED ( Table1[Vendor] ) ) + 1
                    ),
                ,
                DESC
            )
                >= Ranking
        )
    )
RETURN
    IF ( ISFILTERED ( Table1[Vendor] ), Acumulada, COUNT ( Table1[Cases] ) )

To See in % Way change the 

 

Img12.png

 

Regards

 




Lima - Peru

View solution in original post

6 REPLIES 6
Vvelarde
Community Champion
Community Champion

@Anonymous

 

hi, try with this:

 

%Cases running total in Vendor =
DIVIDE (
    CALCULATE (
        COUNT ( 'Table1'[Cases] ),
        FILTER (
            ALLSELECTED ( 'Table1'[Vendor] ),
            ISONORAFTER ( 'Table1'[Vendor], MAX ( 'Table1'[Vendor] ), DESC )
        )
    );
    CALCULATE ( COUNT ( Table1[Cases] ), ALLSELECTED ( Table1 ) )
)

 

Img.png

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Anonymous
Not applicable

Hi VIctor

 

thanks for your reply, it helped, but i am not there yet

the first picture shows the result in PBI

 

 

sup1.PNG

This one here is from Excel, which should be the correct one 

 

sup2.PNG

 

%Cases running total in Vendor =

DIVIDE ( CALCULATE

( COUNT ( 'Case Reg'[Case Number] );

FILTER ( ALLSELECTED ( 'Case Reg'[Supplier]); ISONORAFTER ( 'Case Reg'[Supplier]; MAX ( 'Case Reg'[Supplier] ); DESC ) ) ); CALCULATE ( COUNT ( 'Case Reg'[Case Number]); ALLSELECTED ( 'Case Reg' ) ) )

 

 

can you by any chance see whats wrong?

@Anonymous

 

Hi, please review this PBIX file.

 

https://drive.google.com/file/d/0B95C8CKdTZE3dHVmajdlQmxqUm8/view?usp=sharing

 

Let me know if works in your case.

 

Regards

 

Victor

Lima - Peru




Lima - Peru
Anonymous
Not applicable

unfortunately i cannot open the file i have an older version of PBI Version: 2.44.4675.521 64-bit (march 2017)

@Anonymous

 

Just made an update to PBI.

 

ImgR.png

 

And the measure look like this:

 

% Running =
VAR Ranking =
    RANKX (
        ALLSELECTED ( Table1[Vendor] ),
        RANKX ( ALLSELECTED ( Table1[Vendor] ), CALCULATE ( COUNT ( Table1[Cases] ) ) )
            + DIVIDE (
                RANKX (
                    ALLSELECTED ( Table1[Vendor] ),
                    CALCULATE ( VALUES ( Table1[Vendor] ) ),
                    ,
                    ASC
                ),
                COUNTROWS ( ALLSELECTED ( Table1[Vendor] ) ) + 1
            )
    )
VAR Acumulada =
    CALCULATE (
        COUNT ( Table1[Cases] ),
        FILTER (
            ALLSELECTED ( Table1[Vendor] ),
            RANKX (
                ALLSELECTED ( Table1[Vendor] ),
                RANKX ( ALLSELECTED ( Table1[Vendor] ), CALCULATE ( COUNT ( Table1[Cases] ) ) )
                    + DIVIDE (
                        RANKX (
                            ALLSELECTED ( Table1[Vendor] ),
                            CALCULATE ( VALUES ( Table1[Vendor] ) ),
                            ,
                            ASC
                        ),
                        COUNTROWS ( ALLSELECTED ( Table1[Vendor] ) ) + 1
                    ),
                ,
                DESC
            )
                >= Ranking
        )
    )
RETURN
    IF ( ISFILTERED ( Table1[Vendor] ), Acumulada, COUNT ( Table1[Cases] ) )

To See in % Way change the 

 

Img12.png

 

Regards

 




Lima - Peru
Anonymous
Not applicable

Hello Victor You are a star 🙂 I was able to update PBI to the June version and after that you last solution worked, thank you very much 🙂

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.