How to get rid of the zero lines in my line graph

[Anonymous]

I have a line graph where I used the plus zero trick to make the line hit zero where there is a null, but now my line graph has a bunch of lines that just have zero throughout. Those need to go away and only keep the values if there was a value greater than zero at least once in the date range. The table to the left is what I'd like to keep in the line graph on the right. It filters on the realized qty > 0, which won't work on the graph, because I'll get dots on the line graph where they actually should be hitting zero.

I'm connected to a cube so creating new columns isn't an option. I appreciate any help on the matter!

Edit: The defintion for Realized Qty is 

Realized Qty =

CALCULATE(

    [Qty],

    'Version'[RecordTypeName] IN { "Actuals" },

    ALL('Version'[RecordTypeNo])

) +0

[ebeery · Accepted solution]

@Anonymous I still think my proposed solution would work.

I've tested it with your data file (note I filtered out R101-6 because it was really skewing the axis).

The TotalRealizedQty measure was as follows:

TotalRealizedQty =
CALCULATE ( SUM ( data[Realized Qty] ), REMOVEFILTERS ( data[Level 3] ) )

[Mikelytics]

Hi duncanh,

maybe the followings helps. Its a workaround but it might help to solve your challenge.

I created some dummy data and wrote a measure which checks the sum of all values while ignoring only the colum which is used as the x-axis. If the sum is not zero then there are non zero values so I show the value. if the sum is zero then I put in BLANK() so that the value will not be shown as a graph

Measure =

VAR

     CHECK = CALCULATE(Sum('Test Data'[Value]),ALL('Test Data'[X Axis]))

RETURN

     IF(CHECK=0, BLANK(), Sum('Test Data'[Value]))

In the picture below you can see on the left the lines without the measure and on the right the lines with the measure

I hope it helps.

Best regards

Mikel

[Anonymous]

Hey thanks for the reply, but your solution looks exactly like just filtering by the measure on my cube. I think my measure definition for Realized Qty is throwing everything off. Here's what it looks like:

Realized Qty =

CALCULATE(

    [Qty],

    'Version'[RecordTypeName] IN { "Actuals" },

    ALL('Version'[RecordTypeNo])

) +0

[parry2k]

@Anonymous here is a blog post that I would highly recommend to go over and understand how this PQ works. it will surely pay off in long run.

[parry2k]

@Anonymous it is not as easy as you expect because you want to control for each proditemno.

so this is what needs to happens:

- check if prodline has sales across all the selected periods, if no then return blank() because we don't want to see that product line at all with zero at the bottom

- check if prodline has sales then get the maximum period (either across all the product lines or that specific product line) and

- finally give the maximum period for that product, anything before that will be zero or whatever period value 

It is pretty doable, throw sample data in a pbix file and I will put the measures together.  

[Anonymous]

Thanks. Here is a link: test.pbix - Google Drive

I just pasted the table data and scrambled the values.

Just to be clear, Proditemno = 276OKC1 can show zeros since it has a non-zero value but 287ICT1 shouldn't show up anywhere on this graph at all. 

[ebeery]

@Anonymous one potential method would be to create a seperate measure specifically for filtering, which calculates the total "Realized Quantity".

For example:

TotalRealizedQty = 
CALCULATE(
    SUM('Table'[Realized Qty]),
    REMOVEFILTERS('DateTable'[MONTH]
))

Then filter the visual using the measure "TotalRealizedQty" > 0

[Anonymous]

Darn I had high hopes for this one. I get the following results, which is the same as filtering by my original measure. Those dots need to go to zero when there isn't data, like in my original graph.

What I think will work is a measure like

sumx(all('Fact Mfg'), [Realized Qty])

but that aggregates over ProdItemNo, and then filtering on that. I just don't know how to write it.

[ebeery · Accepted solution]

@Anonymous I still think my proposed solution would work.

I've tested it with your data file (note I filtered out R101-6 because it was really skewing the axis).

The TotalRealizedQty measure was as follows:

TotalRealizedQty =
CALCULATE ( SUM ( data[Realized Qty] ), REMOVEFILTERS ( data[Level 3] ) )

[Anonymous · Accepted solution]

I figured it out! I changed the removefilters function to pass the entire date table and not just the column and everything worked!

You definitely paved the way for this!

[Anonymous]

I'm sharing my original measure definition for Realized Qty. I think that is what is throwing everything off: 

Realized Qty =

CALCULATE(

    [Qty],

    'Version'[RecordTypeName] IN { "Actuals" },

    ALL('Version'[RecordTypeNo])

) +0

[Anonymous]

Yes that is exactly the outcome that I need. Maybe it was the transition of the extracted data, but going against my cube, I still get this and your measure doesn't work. The measure logic is visible at the top. Those dots need to hit zero: 

[parry2k]

@Anonymous find the maximum period in your table and then fix the measure to no go beyond the maximum period.

[Anonymous]

I know what you are saying, but that wont solve the issue with period 1 -7 that have the zero lines as well.