Troubleshooting DAX Totals with a Switch

DAX DAX Calculations
1

Good day.
This is my first post to the forum.
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

I would like to have line times that show how many of each type of fruit is sold and a total row that sums up all apples sold (green apples + red apples = total).

It could be that we start selling purple apples next year. In that case, I want to be able to add purple apples as product code 1.3. That would allow me not have to update my DAX for total apples since I want it to pull the total using something like the following.

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

This would allow me not to have to update my DAX calculations for totals each time we sell a new type of produce.

I am able to get the line items to present properly.
My totals do not show.

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

The desired outcome is to see that Total apples returns 21 in the template without having to write a DAX calc like the following.

Total apples = [Total green apples] + [Total red apples]

Below is a screenshot of what I would like to achieve:
image

Source data:
Produce_Report_Sample.pbix (37.1 KB)
Sales_Data.xlsx (9.1 KB)
Template.xlsx (9.1 KB)

I realize everyone is busy. As your schedules permit, I would appreciate any guidance in pointing out my mistake(s).

Thank you.
-Nilrups

2

Hi @Nilrups,

Welcome to the forum.
Thanks for providing the data and mock up file. :+1:

See if this works for you. Since you are already using this construct:
LEFT('Sales Data'[Product Code], 2) ="1.")

I’ve moved that to PQ as new attribute in your Template

image
image939×257 12.4 KB

Also notice that you had a blank line because “3.3” was not present in your Template, this has now been added with a total line, as you can see above.

.
With that in place, I created this switch measure.

Total_Sold v2 =
VAR _t = SELECTEDVALUE( Template[Type], "gt" )
VAR _p = SELECTEDVALUE( Template[Prod.] )
VAR _st =
    CALCULATE( [Total_Sold],
        ALLSELECTED( Template ),
        Template[Type] = "2",
        Template[Prod.] = _p
    )
VAR _gt =
    CALCULATE( [Total_Sold],
        ALLSELECTED( Template )
    )
RETURN

SWITCH( TRUE(),
    _t = "2", [Total_Sold],
    _t = "1", _st,
    _t = "gt", _gt
)

with this result

image

.
Here’s your sample
Produce_Report_Sample.pbix (40.2 KB)

I hope this is helpful

1 Like
3

@Melissa ,
Good day.
Thank you very much for your thoughtful and prompt response.
I have read through it a couple of times.
I think it will be a couple more times before I completely understand it.
You created the result I asked for so I am checking it as solved.
You are certainly an expert.

Thank you!!!

Nilrups

4

@Melissa ,
Good day again.
As I thought about the excellent solution you provided, is it possible to create DAX that would eliminate the need for a Type variable and instead use the Product Code as a filter in the Template? This would allow the creation of a structure/taxonomy in the Product Code.

For example:

  1. = Grapes
    1.1 = red grapes
    1.2 = green grapes
    1.1.1 = red grapes with seeds
    1.1.2 = red grapes without seeds
    1.2.1 = green grapes with seeds
    1.2.2 = green grapes without seeds

The desired effect would be that I could use portions of the Product Code using LEFT() to derive my subtotals and totals in the template.
Using % as a representative wildcard,
If the SELECTEDVALUE is
1.% would apply to all grapes.
1.1% would apply to all red grapes (seeded and non seeded)

Was there something wrong in my original DAX that prevented this from happening?

If the is inappropriate forum follow up, please accept my apologies and disregard my questions.

-Nilrups

5

Hi @Nilrups,

I’d be happy to look into that.
Since that is a different requirement, could you open a new topic?

Thanks!