How to calculate running total on a pivoted column

[ellen_s]

Hi everyone so this is the table I'm working with. I have pivoted the data on all the PLs. The PL1 column is basiclly a sum column of PL1.

To calculate the ranking column "PL1 Rank" I used this formula:

PL1 Rank = RANKX(ALLSELECTED(Supplier[Supplier_Name]),CALCULATE(SUM(Invoice_Data[PL1])))

Obviously the column "PL1 Cumu." is not working right now. In this column I want to display the running total based on the "PL1" column.

Thanks!

[tamerj1]

Hi @ellen_s 
As per my undestanding of the problem I believe this should work

PL1 Comu. =
VAR CurrentRank = [PL1 Rank]
VAR T1 =
    SUMMARIZE ( ALL ( Invoice_Data ), Supplier[Supplier_Name] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "Count", CALCULATE ( SUM ( Invoice_Data[PL1] ) ),
        "Rank", [PL1 Rank]
    )
VAR T3 =
    FILTER ( T2, [Rank] <= CurrentRank )
RETURN
    SUMX ( T3, [Count] )

[ellen_s]

Hi thank you that worked but everytime I want to sort any column in the table its taking more than a minute to update. Is there a way to make it faster?

Thanks,

Ellen

[tamerj1]

@ellen_s 
If the supplier ranking is not dependant on other selections (Date for eaxample) then it can be added as a new calculated column. This will make everything much more faster.

[ellen_s]

I have a measure for ranking. The formula is:

PL1 Rank = RANKX(ALLSELECTED(Supplier[Supplier_Name]),CALCULATE(SUM(Invoice_data[PL1])))

I don't think its dependent on anything else besides just PL1 value. Would this work in making the query faster?

Thanks,

Ellen

[tamerj1]

@ellen_s 
Yes. I believe it can but the result won't be dynamic as you may expect. For example If you select a date (or date period) or you slice by region or product category then the ranking won't change because it has to be fixed based on a predefined conditions. In this case for example the ranking can be based on the total invoices value of each supplier (including all dates, all regions, all categories etc..). If your data does not include such attributes or you are just not interested to obtain such results then you may send me sample dummy or insensitive data to create a new calculated column in the 'Supplier' table that gives each supplier a ranking based on his total invoices value. The running total of the report would then be a simple and efficient task.

[tamerj1]

Hi @ellen_s 
Applogies for yesterday's confusion. I was not on my computer at that time.
I found one solution which seems to be working. You need to be carefull if you have big data. However it is worth trying

PL1 Comu. = 
VAR CurrentRank = [PL1 Rank]
VAR T1 =
    SUMMARIZE ( 
        ALL ( Invoice_Data ), 
        Supplier[Supplier_Name],
        "Amont", [PL1]
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "Rank", [PL1 Rank]
    )
VAR T3 =
    FILTER (
        T2,
        [Rank] <= CurrentRank
    )
RETURN
SUMX (
    T3,
    [Amont]
)

I tried the code on a small sales table (77,000) rows and it worked but little slow. Please try on yours and let me know. Thanks and have a great day!

[ellen_s]

Thanks but I tried it and think it would only work if [PL1] is a measure which it is not. I'm getting "Column 'PL1' cannot be found or may not be used in this expression." I also tried Invoice_Data[PL1] but it did not work either. I tried to use the auto complete on powerbi, it is only suggesting formulas and measures. I tried sum(Invoice_Data[PL1]) but its outputting wrong numbers.

Also I feel like this is making the table super slow and we want to sort the table by any column quickly so I dont know if this approach would work well for our application.

Thank you and please let me know if you have any ideas?

Ellen

[tamerj1]

Can you please try replacing [PL1] with SUM ( Invoice_Data[PL1] )

the rest of the code remains the same 

[tamerj1]

IF PL1 is a column does that mean you have only one value for each brand?

[ellen_s]

As I said before I tried the sum and it output wrong values.

PL1 is a column that I got from pivoting the table on invoice ID. There could be many rows for PL1 for each supplier, thats why I use the sum function in the table so it aggregates the table and tell me how many rows I have for PL1 (which represents how many invoices PL1 has for each supplier).

Thanks and hope that make sense,

Ellen

[tamerj1]

Ok the 

Can you please try replacing [PL1] with COUNTROWS ( DISTINCT ( Invoice_Data[Invoice ID] ) )

the rest of the code remains the same 

[ellen_s]

I tried it and it didn't work it just output random values that dont make sense. I looked back on my previous comment and I think it wasn't very clear. So I pivoted based on plant number and each row is a seperate invoice number. Right now we are working with PL1 there are PL2, PL3, PL4 etc. sum of PL1 aggregated by supplier name gives me the number of invoices for each supplier. Hopefully that make sense.

Thanks

[tamerj1]

Hi @ellen_s 

you can try the following Measure

PL1 Cumu. =
VAR CurrentRank =

MIN ( yourtablename[PL1 Rank] )

RETURN

SUMX (

ALL ( yourtablename[Supplier Name] ),

CALCULATE (

SUM ( yourtablename[PL1] ),

yourtablename[PL1 Rank] >= CurrentRank

)

)

[ellen_s]

Hi I tried it just now and I'm getting this error: The MIN function only accepts a column reference as the argument number 1.

[tamerj1]

my mistake I thought it was a colum 🙂

Not sure if it works but you may try 

VAR CurrentRank = [PL1 Rank]

RETURN

SUMX (

ALL ( yourtablename[Supplier Name] ),

CALCULATE (

SUM ( yourtablename[PL1] ),

[PL1 Rank] >= CurrentRank

)

)

[tamerj1]

Hi @ellen_s 

can you please share the code you have tried? I mean with the actual table name

[ellen_s]

PL1 Cumu.=
VAR CurrentRank =MIN ([PL1 Rank])RETURN

SUMX (

ALL ( Supplier[Supplier_Name] ),

CALCULATE (

SUM ( Invoice_Data[PL1] ),

[PL1 Rank] >= CurrentRank

)

)

Thanks 🙂

[tamerj1]

my mistake I thought it was a colum 🙂

Not sure if it works but you may try 

VAR CurrentRank = [PL1 Rank]

RETURN

SUMX (

ALL ( yourtablename[Supplier Name] ),

CALCULATE (

SUM ( yourtablename[PL1] ),

[PL1 Rank] >= CurrentRank

)

)

[ellen_s]

Thanks for that. Now the error is gone but now there is a new one: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." Any ideas?

Thanks