% Running total in vendor DAX

[Anonymous]

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

;

[Vvelarde]

@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 ) )
)

Regards

Victor

Lima - Peru

[Anonymous]

Hi VIctor

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

the first picture shows the result in PBI

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

%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?

[Vvelarde]

@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

[Anonymous]

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

[Vvelarde · Accepted solution]

@Anonymous

Just made an update to PBI.

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 

Regards

[Anonymous]

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 🙂