Forum Discussion

MarioGreco's avatar
MarioGreco
Frequent Visitor
3 years ago

Calculate running total within each row subtotal

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 cu...
MarioGreco's avatar
MarioGreco
Frequent Visitor
3 years ago

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.

MarioGreco's avatar
MarioGreco
Frequent Visitor
3 years ago

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]

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    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?