topic Re: How to calculate running total on a pivoted column in DAX Commands and Tips

How to calculate running total on a pivoted column

ellen_s — Wed, 02 Mar 2022 19:29:29 GMT

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!

Re: How to calculate running total on a pivoted column

tamerj1 — Wed, 02 Mar 2022 19:47:31 GMT

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

)

Re: How to calculate running total on a pivoted column

ellen_s — Wed, 02 Mar 2022 20:04:22 GMT

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.

Re: How to calculate running total on a pivoted column

tamerj1 — Wed, 02 Mar 2022 20:09:00 GMT

Hi ellen_s

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

Re: How to calculate running total on a pivoted column

tamerj1 — Wed, 02 Mar 2022 20:17:51 GMT

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

)

Re: How to calculate running total on a pivoted column

ellen_s — Wed, 02 Mar 2022 20:20:58 GMT

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

SUMX (

ALL ( Supplier[Supplier_Name] ),

CALCULATE (

SUM ( Invoice_Data[PL1] ),

[PL1 Rank] >= CurrentRank

)

Thanks 🙂

Re: How to calculate running total on a pivoted column

tamerj1 — Wed, 02 Mar 2022 20:30:43 GMT

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

)

Re: How to calculate running total on a pivoted column

ellen_s — Wed, 02 Mar 2022 20:42:42 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Thu, 03 Mar 2022 06:04:01 GMT

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!

Re: How to calculate running total on a pivoted column

ellen_s — Thu, 03 Mar 2022 13:54:30 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Thu, 03 Mar 2022 14:09:57 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Thu, 03 Mar 2022 14:30:16 GMT

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

the rest of the code remains the same

Re: How to calculate running total on a pivoted column

ellen_s — Thu, 03 Mar 2022 14:35:39 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Thu, 03 Mar 2022 14:42:15 GMT

Ok the

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

the rest of the code remains the same

Re: How to calculate running total on a pivoted column

ellen_s — Thu, 03 Mar 2022 20:08:11 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Fri, 04 Mar 2022 06:37:41 GMT

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

Re: How to calculate running total on a pivoted column

ellen_s — Fri, 04 Mar 2022 13:37:30 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Fri, 04 Mar 2022 13:44:08 GMT

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.

Re: How to calculate running total on a pivoted column

ellen_s — Fri, 04 Mar 2022 13:48:30 GMT

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

Re: How to calculate running total on a pivoted column

tamerj1 — Sat, 05 Mar 2022 03:56:51 GMT

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.