Get Row category value next to all sub-category values.

[Anonymous]

Hello, I was wondering if anyone could help me, I've tried many different things and can't get it to work in my favor.

In simple terms: I want to create a new column that that gives me the total category value next  all subtotals value.

  Above is a dumby chart with the calculations I have already done, as it is now: it's just pulls same data over(not how I want it).

(Total should be Vegetable Total+Fruit Total, please ignore the 268 and 828)

thank you for any suggestions!

[v-alq-msft]

Hi, @Anonymous 

You may try creating a calculated column as below.

Column =
CALCULATE (
    SUM ( Table[values] ),
    FILTER (
        ALL ( Table ),
        Table[category] = EARLIER ( Table[category] )
            && Table[Quarter] = EARLIER ( Table[Quarter] )
    )
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a formula like

calculate(sum(Table[values]), filter(allselcted(Table), Table[category] =max(table[category]) && Table[Quarter] =max(table[Quarter])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

This worked however Is there any way to translate this into a calculated column, I do not want the data to dynamically change with filters, I want to create a Category total for all subtotals in the Data view, any suggestions on how to do it?