Subtotal not showing in SWITCH () Using a product code taxonomy/structure and LEFT() in a SWITCH() for Totals and Subtotals in a report

DAX DAX Calculations
1

Good day.

After going through the Financial Statements course, I am building my own statement.
In this example I want a statement that helps me calculate and report on the produce sold.
I have:

  • A measure table
  • A sales data table
  • A report template

The above are attached.

I would like to have line times that show how many of each type of produce
Produce_Report_Sample.pbix (36.6 KB)
Sales_Data.xlsx (9.2 KB)
Template.xlsx (9.6 KB)
is sold and line items that subtotal and total rows that sums up all the types of produce.

I have made an attempt, but my subtotals in my template return no value. The DAX for the subtotals works as I have set up card visual to show the total by type of produce is returning a value.

My goal is to have the template leverage a format for the Product Code from the Sales table with an implied hierarchy so that as I parse the Product Code in the DAX it allows me to calculate and display subtotals in the template.

For example:
Product Code
3. = Grapes
3.1 = green grapes
3.2 = red grapes
3.1.1 = green grapes with seeds
3.1.2 = green grapes without seeds
3.2.1 = red grapes with seeds
3.2.2 = red grapes without seeds

The desired effect would be that I could parse portions of the Product Code using LEFT() to derive my subtotals and totals in the template.

If the SELECTEDVALUE is
Left(Products Code,2) = “3.” would apply to all grapes.
Left(Product Code, 3) = “3.1” would apply to all green grapes (seeded and non seeded)

In the example I have built, I am able to get the line items to present properly.
My subtotals do not show.

I put my total measures on cards to test that they are calculating properly.

Thank you in advance for any guidance.

1 Like
2

Hi @Nilrups,

Based on what you described and mentioned in the previous post, I thought you might want to examine hierarchies. That will get you a result like this:

image

Note my data is different because I created this sample.

The model itself is simple but requires a specific setup of the dimension table
image
.
This is the Product dimension, which includes calculated columns.

image
image779×174 5.86 KB

.
You can read up and learn all about this method, here:
Parent-child hierarchies – DAX Patterns

.
Here’s the sample file
Produce_Report_Sample.pbix (54.3 KB)

I hope this is helpful.

3 Likes
3

Good day.

@Melissa , I was not aware of hierarchies and DAX patterns Thank you for educating me and the link to the resource. I placed a heart in appreciation.

To further build on the post/question for anyone who is reviewing.
While hierarchies are one path, I believe my problem is simpler and hierarchies aren’t the answer to my question.

When I use the following DAX:

Items_Sold = 
VAR CurrentItem = SELECTEDVALUE( Template[Product Code])
VAR stotal = SELECTEDVALUE(Template[Product Item])

Return
SWITCH( TRUE(),
    CurrentItem = "1.0", [Total_Apples],
    CurrentItem = "2.0", [Total_Berries],
        CALCULATE([Total_Sold], FILTER('Sales Data', 'Sales Data'[Product Code] = CurrentItem) ) )

I get this result (no values in the “total” lines )
image

When I remove my DAX calculations that are totals [Total_Apples], Total_Berries] and replace them with the strings “Apple” and “Berry” (please see following)

Items_Sold = 
VAR CurrentItem = SELECTEDVALUE( Template[Product Code])
VAR stotal = SELECTEDVALUE(Template[Product Item])

Return
SWITCH( TRUE(),
    CurrentItem = "1.0", "Apple",
    CurrentItem = "2.0", "Berry",
        CALCULATE([Total_Sold], FILTER('Sales Data', 'Sales Data'[Product Code] = CurrentItem) ) )

I get the following: (values in the “totals” lines)
image

String values appear in the “totals” lines, but by the results of my DAX calculations do not.

My DAX calculations are as follows:

Total_Sold = CALCULATE(SUM('Sales Data'[Quantity]))
---------------------
Total_Apples = 
    CALCULATE(
        [Total_Sold], LEFT('Sales Data'[Product Code], 2) ="1.")

---------------------------------
Total_Berries = 
          CALCULATE(
               [Total_Sold],LEFT('Sales Data'[Product Code],2) ="2.")

Is there a better way to write my DAX calculations for totals so that the numbers show up in the “totals” line?

4

Hi @Nilrups,

First the reason why you weren’t seeing the card value in the table is because of the filter context generated by the table/matrix visual. To overcome that you could do something like this:

image
.
BUT if hierarchies are out, can we agree that using less ‘hard coded’ measures is the better option? As far as I can tell all subtotal rows have a Product Code that ends on .0 if that is true for all your data, consider this instead for a table/matrix visual.

image

The measure:

Items_Sold v2 = 
VAR CurrentItem = SELECTEDVALUE( Template[Product Code] )
VAR _Total = RIGHT( CurrentItem, 2 ) = ".0"
VAR _Item = LEFT( CurrentItem, 2 )
VAR _Context = VALUES( Template[Product Code] )
RETURN

SWITCH( TRUE(),
    _Total = TRUE, CALCULATE( [Total_Sold], REMOVEFILTERS( Template ), LEFT('Sales Data'[Product Code], 2) = _Item ),
    _Total = FALSE && COUNTROWS(_Context) =1, CALCULATE( [Total_Sold], REMOVEFILTERS( Template ), LEFT('Sales Data'[Product Code], 2) = _Item , _Context),
    [Total_Sold]
)

I hope this is helpful

2 Likes
5

@Melissa ,
Thanks you for not only educating me once again, but also for your insights!
Thank you!

1 Like
6

Glad I could help. :+1: