topic Re: Calculate running total within each row subtotal in DAX Commands and Tips

Calculate running total within each row subtotal

MarioGreco — Fri, 17 Mar 2023 18:18:29 GMT

I have a matrix in Power BI with field parameters in the rows. There are 11 field parameters in total, I'm only showing 3 of them for simplicity (City, Year, ABC).

What I need to calculate is the cumulative sales (X) from biggest to smallest number (Pareto style), for each row subtotal, for each random selection order of field parameters. This means that if first I select Year, then ABC, then City, the cumulative calculation should adapt to this.

An example is in this picture:

I know how to calculate the cumulative sales for each subtotal by doing CALCULATE([Sales], ALLSELECTED(ABC)) for example. But the challenge is in doing this calculation for all the possible selection orders of the field parameters. I saw something that might help which is ROLLUPADDISSUBTOTAL, but don't yet know fully how to use it.

All the help will be deeply appreciated! Thanks in advance!

Re: Calculate running total within each row subtotal

lbendlin — Sun, 19 Mar 2023 23:56:59 GMT

Kudos for tackling such a complex topic. ROLLUPADDISSUBTOTAL is a visual rendering hint, it won't really help you here. But what you can do is examine the formulas that go into the definition of your field parameter. That should give you something to work with. If you have DAX Studio you can also examine the query that is produced for the visual.

Re: Calculate running total within each row subtotal

MarioGreco — Mon, 20 Mar 2023 20:49:06 GMT

Another power user shared with me the link of a video:
Power BI Window: Pareto Analysis Again, 80% of sales, Order by Measure when REL position is used: https://youtu.be/GpoITi_tRIw
I tried to apply it to my case but to no avail. But it's probably because I'm not entirely familiar with the Dax function WINDOW yet. The function is magnificent and I think it may work, but still any help with would be very much appreciated!

Re: Calculate running total within each row subtotal

MarioGreco — Thu, 23 Mar 2023 09:36:04 GMT

Hi Ibendlin! Thanks for your reply! I checked this in Dax Studio copying the Dax query from the Performance analyzing tool.

The result is a table that brings all sales and subtotals for the parameters selected.
The main issue is that here the parameters selected are hard coded into the Dax query in the order they where selected, there is no part of the query that uses like a relative reference to the selected values of field parameters or the order of selection. I'll post the Dax in the next reply.

I also tried CONCATENATEX(values(fieldParameters), fieldParameters, ",") to get the list of selected parameters (this works) and use this as a filter but I couldn't use it as a filter and the selection order of parameters didn't correspond to the order of concatenation, and order is a main issue in this case.

Re: Calculate running total within each row subtotal

MarioGreco — Thu, 23 Mar 2023 11:09:40 GMT

The DAX Query:

// DAX Query
DEFINE
VAR __DS0FilterTable = //These are the Selected fields in the slicer, included in the field parameter table. They come hard coded
TREATAS(
{"'Zonas'[Zona]",
"'Grupos y Jerarquías'[Grupo artículos]",
"'Grupos y Jerarquías'[Jerarquía 1]",
"'Canales'[Canal]"},
'Param. Ventas'[Parámetros Campos] //This are some of the field parameters table fields
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'Zonas'[Zona], "IsGrandTotalRowTotal",
'Grupos y Jerarquías'[Grupo artículos], "IsDM1Total",
'Grupos y Jerarquías'[Jerarquía 1], "IsDM3Total",
'Canales'[Canal], "IsDM5Total"
),
__DS0FilterTable,
"Venta_Neta", 'Ventas 2M KOFI'[Venta Neta],
"Pareto", 'Ventas 2M KOFI'[Pareto]
)

VAR __DS0CoreTableByDM1 =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(__DS0Core),
AND(AND([IsDM1Total] = TRUE, [IsDM3Total] = TRUE), [IsDM5Total] = TRUE)
)
),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Zonas'[Zona]", 'Zonas'[Zona],
"SortBy_DM1_0", [Venta_Neta]
)

VAR __DS0CoreTableByDM3 =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(KEEPFILTERS(__DS0Core), AND([IsDM3Total] = TRUE, [IsDM5Total] = TRUE))
),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Zonas'[Zona]", 'Zonas'[Zona],
"IsDM1Total", [IsDM1Total],
"'Grupos y Jerarquías'[Grupo artículos]", 'Grupos y Jerarquías'[Grupo artículos],
"SortBy_DM3_0", [Venta_Neta]
)

VAR __DS0CoreTableByDM5 =
SELECTCOLUMNS(
KEEPFILTERS(FILTER(KEEPFILTERS(__DS0Core), [IsDM5Total] = TRUE)),
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"'Zonas'[Zona]", 'Zonas'[Zona],
"IsDM1Total", [IsDM1Total],
"'Grupos y Jerarquías'[Grupo artículos]", 'Grupos y Jerarquías'[Grupo artículos],
"IsDM3Total", [IsDM3Total],
"'Grupos y Jerarquías'[Jerarquía 1]", 'Grupos y Jerarquías'[Jerarquía 1],
"SortBy_DM5_0", [Venta_Neta]
)

VAR __DS0PrimaryWithSortColumns =
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
__DS0Core,
__DS0CoreTableByDM1
),
__DS0CoreTableByDM3
),
__DS0CoreTableByDM5
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0PrimaryWithSortColumns,
[IsGrandTotalRowTotal],
0,
[SortBy_DM1_0],
0,
'Zonas'[Zona],
1,
[IsDM1Total],
0,
[SortBy_DM3_0],
0,
'Grupos y Jerarquías'[Grupo artículos],
1,
[IsDM3Total],
0,
[SortBy_DM5_0],
0,
'Grupos y Jerarquías'[Jerarquía 1],
1,
[IsDM5Total],
0,
[Venta_Neta],
0,
'Canales'[Canal],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
[SortBy_DM1_0] DESC,
'Zonas'[Zona],
[IsDM1Total] DESC,
[SortBy_DM3_0] DESC,
'Grupos y Jerarquías'[Grupo artículos],
[IsDM3Total] DESC,
[SortBy_DM5_0] DESC,
'Grupos y Jerarquías'[Jerarquía 1],
[IsDM5Total] DESC,
[Venta_Neta] DESC,
'Canales'[Canal]

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS(
{"'Zonas'[Zona]",
"'Grupos y Jerarquías'[Grupo artículos]",
"'Grupos y Jerarquías'[Jerarquía 1]",
"'Canales'[Canal]"},
'Param. Ventas'[Parámetros Campos]
)

VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'Param. Ventas',
'Param. Ventas'[Parámetros Campos],
'Param. Ventas'[Parámetros Orden],
'Param. Ventas'[Parámetros]
),
KEEPFILTERS(__DS0FilterTable)
)

VAR __DS0BodyLimited =
TOPN(
152,
__DS0Core,
'Param. Ventas'[Parámetros Orden],
1,
'Param. Ventas'[Parámetros Campos],
1,
'Param. Ventas'[Parámetros],
1
)

EVALUATE
__DS0BodyLimited

ORDER BY
'Param. Ventas'[Parámetros Orden],
'Param. Ventas'[Parámetros Campos],
'Param. Ventas'[Parámetros]

Re: Calculate running total within each row subtotal

MarioGreco — Thu, 23 Mar 2023 11:13:49 GMT

tamerj1 , FreemanZ , andhiii079845 I see you are the Top Solution Authors from last month, do you have any ideas for this case?

Re: Calculate running total within each row subtotal

lbendlin — Thu, 23 Mar 2023 12:17:40 GMT

note the nested joins in the DAX. Have a look at the query plan in DAX Studio. This could be a very costly query. And this is when it is hard coded...

This drives home the point that Field parameters are not really part of Vertipaq, they are part of the UI layer. The UI materializes the selected values before handing this over to the formula and storage engines. Not sure if SELECTEDVALUE can help here.

What would happen if you run Field Parameters against measures? Would SELECTEDMEASURENAME be available?