Switch Measures on Tables

[Gryffindor]

i’m trying to do this where some rows have sub groups and some doesn’t
i want to add cumulative total to rows which doesn’t have subgroup. How to acheive this.
Let me share the Dax code on making this work.
Acumulative Total =
VAR _headlineselect = SELECTEDVALUE(Headlines[Orden])
VAR _result =
CALCULATE(
[Total Amount],
ALL(Headlines),
Headlines[Orden] <= _headlineselect
)

RETURN _result

Total P&L =
VAR _type = SELECTEDVALUE(Headlines[Type])
VAR _detail = SELECTEDVALUE(Headlines[Detail])
VAR _filter = ISFILTERED(Subgroup[Subgroup])
VAR _result =
SWITCH(
TRUE(),
_filter = TRUE() && _detail = 0, BLANK(),
_type = 1, [Absolute Total],
_type = 2, [Acumulative Total]
)

RETURN _result

How do i acheive the same in Quicksight.

[Xclipse]

Hi @Gryffindor

The DAX code you shared for creating a cumulative total and determining if a row has subgroups can be adapted conceptually for QuickSight using its functions and aggregations.

To add a cumulative total to rows without subgroups, you’ll need to leverage calculated fields and conditionally apply cumulative calculations using some custom logic in QuickSight.

Subgroups and Parent Rows - You can create a calculated field named IsSubgroup to identify if a row has a subgroup or not, based on a specific column or a value that indicates whether it is a detail row.

Example: (Syntax may vary - Replace the field name from your dataset)

ifelse(isNull({Subgroup}), 'Parent', 'Subgroup')

Create the Cumulative Total: Use the sumOver function to calculate the cumulative total for parent rows.

Example: (Syntax may vary - Replace the field name from your dataset)

ifelse(
    {IsSubgroup} = 'Parent',
    sumOver(sum({Total Amount}), [{Orden}]),
    null
)

To mirror the logic in your DAX code for determining if it should show the absolute or cumulative total, you can create another calculated field.

Example: (Syntax may vary - Replace the field name from your dataset)

ifelse(
    isNull({Subgroup}),
    {Cumulative Total},   -- Cumulative Total if it's a parent row
    {Absolute Total}      -- Absolute Total if it's a subgroup
)

By combining calculated fields and built-in functions, you can replicate similar outcomes.

[Gryffindor]

Not able to bring value to subgroup

is this right way?

[Xclipse]

Hi @Gryffindor

Can you please help mw with sample data, if you can recreate this issue in Arena using sample non-sensitive data, I can take a deeper look to see what is going on.

[Gryffindor]

Table view
Shared the link.

[Xclipse]

Hi @Gryffindor

Can you please correct the values in the text box that you updated in the analysis? They do not seem to match the dummy data that you loaded.

[Gryffindor]

Hi @Xclipse
I have updated . Could you check it out.
Thanks

[Xclipse]

Hi @Gryffindor

Please try the following calculation for the Total Amount and Cumulative Total.

The second value did not match exactly with what you entered in the text box, and because of that, the running sum (Cumulative Total) did not match.

Total Amount - sumOver(sum({Amount, $}), [{Orden[headline]}])
Cumulative Total - runningSum(sum({Amount, $}), [{Orden[headline]} ASC])

[Gryffindor]

Hi @Xclipse .
I was able to get cumulative total. I need another solution where i’m trying to add a new column where in if type=1 i want to display “Total” calculation and if Type=2 i want to display “cum” calculation value.
Switch_calc=ifelse({Type}=1,{abs total},{cum}) (This calculation doesn’t work)
This is how like i want to achieve. Let me know how it can be achieved

[Xclipse]

Hi @Gryffindor

I’ve responded to the new community question that you posted.