Sum by Individual Selection and Sum by Group

[kamil947]

Hello,

I'm trying to show a dash that is built on the food slicer. Once the food is selected, I'd like to display the amount of that specific food and the amount in the specific group. For example, if I select Apple - I would like the 3 for the amount of apples and 14 for the amount of fruit total to display. What DAX would have to be used to update the group based on the food selection? Similarly, if I change the slicer to Chips, the amount for chips updates correctly to 2, but what DAX would I use to have the group automatically be updated to Snack and show the total of 3?

Thank you!

Food	Group	Amount
Apple	Fruit	3
Banana	Fruit	4
Kiwi	Fruit	7
Chips	Snack	2
Chocolate	Snack	1
Chicken	Meat	9

[kamil947]

I believe I just answered this question myself - you would add a the date column to the ALLEXCEPT statement. Thank you so much to everyone. Brilliant support!

[kamil947]

This support has been amazing, thank you to everyone.

@TheoC If I wanted to add a date aspect to this with a date slicer and dates next to the food, how would that alter the measure you provided?

[TheoC]

@kamil947 I definitely recommend using a Date table. They can be easily generated using DAX and the "New Table" button on the Model ribbon.

Here is a link to a simple DAX table:  https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

From there, you will need to add additional a column of data into your fact table to enable relevance to occure between you Date table and Fact table.

Otherwise, you can just add the Date data to you existing table: it just wouldn't really be "best practice" and you may find that some standard approaches using DAX and dates together may not work perfectly (I.e. you may require workarounds and all thT).

[TheoC · Accepted solution]

@kamil947, apologies. I misread the part that you wanted the "Sum of Group" in the other Card. 

Although @Kevin_Harper has provided a very good output in terms of Calculated Columns, there is a challenge with using this for the output as evident below.  The total of the Calculated Column totals the amounts at the row level and therefore can overstate the actual output.  

I recommend using a Measure as per below which provides the total amount of 26 when nothing is selected but also achieves the group subtotal in the Card visual itself.

The Measure is as follows:

Measure = 

    SUMX ( 
        VALUES ( tableFood[Group] ) , 
        CALCULATE ( SUM ( tableFood[Amount] ) , ALLEXCEPT ( 'tableFood' , tableFood[Group] ) )
        )

All the best.

[v-jayw-msft]

Hi @kamil947 ,

You will need to create an independent slicer table.

slicer = DISTINCT('Table'[Food])

Use this table as slicer and then create two measures as below for selected food and group.

food_amount = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Food]=SELECTEDVALUE('slicer'[Food])))

group_amount = 
var _group = CALCULATE(MAX('Table'[Group]),FILTER(ALL('Table'),'Table'[Food]=SELECTEDVALUE(slicer[Food])))
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Group]=_group))

Result would be shown as below.

Best Regards,

Jay

[TheoC]

@kamil947 much easier solution here:

The Measure is as follows:

Measure = 

    SUMX ( 
        VALUES ( tableFood[Group] ) , 
        CALCULATE ( SUM ( tableFood[Amount] ) , ALLEXCEPT ( 'tableFood' , tableFood[Group] ) )
        )

[Kevin_Harper]

You can create a column on your table using the ALLEXCEPT function.

Try creating this column on your table:

group subtotals = CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table', 'Table'[Group]))

Here is the result:

[YukiK]

This DAX code works theoritically, but it looks like a calculated table can't take dynamic values, so I don't think this is possible in DAX. If there is a workaround for this, I'd like to know!

Grouped Amount =

VAR __GroupOnTheSameRow =

// SELECTEDVALUE ( 'Table'[Group] ) <- this won't work

"Fruit" // works with a fixed value

VAR __FilteredTable =

CALCULATETABLE ( 'Table', 'Table'[Group] = __GroupOnTheSameRow )

VAR __Amount =

SUMX (

SUMMARIZECOLUMNS ( __FilteredTable, "Amount", SUM ( 'Table'[Amount] ) ),

[Amount]

)

RETURN

__Amount

If you find this post helpful, please give it a thums up!

[kamil947]

Hi @TheoC and thank you.

Is there anyway to just have the 'Food' in the slicer without having to expand within the group and then selecting the food? I ask because I'm looking to build upon this and eventually expand the groupings.

When I select Kiwi using the setting you recommended, I still have both my cards showing the same count when I'd like to show one as the food count for Kiwi (7) and the other the Fruit count (14).

[TheoC]

Hi @kamil947 

Just remove the "Group" from the slicer and you can filter using Food names.

Also, in terms of your Card visuals, I am unsure how you're getting those numbers. Did you drag the "Amount" into a Card visual or did you try to create measures?  With what you are trying to achieve, you do not need measures. All you need to do is drag the Amount field into a Card visual and ensure you have the Amount field as a "Whole Number" data type.

[TheoC]

@kamil947, apologies. I misread the part that you wanted the "Sum of Group" in the other Card. 

Although @Kevin_Harper has provided a very good output in terms of Calculated Columns, there is a challenge with using this for the output as evident below.  The total of the Calculated Column totals the amounts at the row level and therefore can overstate the actual output.  

I recommend using a Measure as per below which provides the total amount of 26 when nothing is selected but also achieves the group subtotal in the Card visual itself.

The Measure is as follows:

Measure = 

    SUMX ( 
        VALUES ( tableFood[Group] ) , 
        CALCULATE ( SUM ( tableFood[Amount] ) , ALLEXCEPT ( 'tableFood' , tableFood[Group] ) )
        )

All the best.

Theo

[TheoC]

Hi @kamil947 

If your table is already structured the way you have presented, then no DAX is needed.

Go to your visuals pane and select the Slicer visual like below and drag the Food and Group field in. 

This will allow you to select the Group and the Fruit, returning the Amount you are after 🙂

Hope this helps.

Theo 🙂