Legend problem with line and stacked column chart

[rpinxt]

Ok this is the situation in a picture:

The columns are relates to volumes. And the categories (from the legend) correspond to the volumes.

The line however is based on FTE and the category field for those lines is empty (blank).

Therefore in the legend (Blank) is showing, but we want this gone.

However when we in the filter pane switch off (Blank) also the line will dissapear!

This because for all the line FTE values the category is blank, so excluding this also excludes the values for the line...

Is there a descent way to fix this??

[rpinxt]

Thanks @mickey64 , but I was able to make the correct measure with calculate and filter 🙂

And indeed this way I can have 3 colunm values and 1 line value and the blank is not bothering anymore.

Thanks a lot for putting me in the correct direction! 👍

[mickey64 · Accepted solution]

How about use measures?

Affiliate = SUMX(FILTER('Table name','Table name'[category]="Affiliate"),'Table name'[volume])

Direct =  SUMX(FILTER('Table name','Table name'[category]="Direct"),'Table name'[volume])

Distributor = SUMX(...

[rpinxt]

Hmm so you say instead of 1 column Y-axis field, make 3 for the separate legend values?

Looks like I can only add 1 column y-axis field.

The line axis could hold more I see but that is of no use.

[mickey64]

I think you can add some measures after you remove 'Category' from 'Column legend'...

[rpinxt]

You are correct.

Will look into this.

Somehow my SUMX is not returning data....

Affiliate_units = SUMX(FILTER('AVN Excel Data','AVN Excel Data'[Category]="Affiliate"),[Units])

So maybe if I get this fixed I can solve it like this.

[mickey64]

How about this formula below?

Affiliate_units = SUMX(FILTER('AVN Excel Data','AVN Excel Data'[Category]="Affiliate"),'AVN Excel Data'[Units])