Problems using the ALL() function

[esuing]

I feel like this is a simple case, but I don't know what's happening. I have the following tables:

ColorTypes:

Types:

Data:

The joins should be pretty obvious (Data[Color] -> ColorTypes[Color], etc). And then I put together a very simple table that sums the Amt field by type and color:

I would like to add a measure that is the sum of the Amt for each type regardless of Color.  I use this function with this result:

Type Total = CALCULATE([Sum Amt],All(Data[Color]))

It should look something like this:

I'm not sure why it's introducing those new records as it doesn't seem to be adhering to the relationships between the three tables. Any help would be appreciated.

Thanks!

Eric

[Anonymous · Accepted solution]

HI @esuing,

It seems like the expression also expands the category groups which do not include records. Perhaps you can add an if statement to your expression to skip the calculation on these blank parts.

Type Total =
VAR result =
    CALCULATE ( [Sum Amt], ALL ( Data[Color] ) )
RETURN
    IF ( [Sum Amt] > 0, result )

Regards,

Xiaoxin Sheng

[esuing]

Thank you! This is what I was looking for any worked in my other application with a much larger dataset really well!

[Mikelytics]

Hi Eric,

Please try: 

Type Total = CALCULATE([Sum Amt],All(ColorTypes[Color])) and when you put the columns in the visual please use the color column from the ColorTypes table

Best regards

Michael

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

[aj1973]

Hi @esuing 

Go to filter Panel 

[esuing]

Hi. Thanks.. this does work, however, I was hoping there was something in the measure itself that would prevent me from having to do this as it seems it would run more efficiently if it didn't return a value for every value in both tables..  The dataset I'm using for work is HUGE (versus this example I just presented in this thread) and so it's very slow doing it this way.

[aj1973]

Use the same columns from the Table ColorTypes into your visual and modify the measure: