Not Displaying Values on Chart

[Arranafc19]

I am working on a power bi line chart and I want to exclude records from my line chart without changing the values by doing so.

I have a dataset which gives me a number of products and the month in which they were sold. When I put this into a table and display the number of sales per month as a percentage I get the below with the "Null" value being the items that are not sold.

I need to be able to display months 1-12 on a line chart , not displaying the null however using them for the overall percentage total. When I filter to show only where month > 0 , I am excluding the cases with no sale date from the percentage calcualation and they all rise. Is there a way to display this on a chart , and just not show certain values ?

[v-frfei-msft]

Hi @Arranafc19 ,

To create a measure as below.

Measure =
VAR alle =
    CALCULATE (
        SUM ( Table1[sales] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[month] <> BLANK () )
    )
VAR su =
    CALCULATE (
        SUM ( Table1[sales] ),
        FILTER ( Table1, Table1[month] <> BLANK () )
    )
RETURN
    IF ( MAX ( Table1[month] ) = BLANK (), BLANK (), DIVIDE ( su, alle ) )

[Arranafc19]

Hi @v-frfei-msft 

Having looked at your solution , it looks as though you are excluding the null values from the percentage calculation which is not what I am looking for .

I need the value of sales for month null to be included in the percentage calcuation but not to be displayed on the chart , as I am trying to get a percentage closed per month of overall.

Is there a way to do this ?

[Cmcmahan]

You can use the same measure for both.

Click the chart, and on the right, select month (or add it if needed) in Visual level filters > Advanced Filtering > Show items when the value: is not blank > Apply Filter

[Arranafc19]

@Cmcmahan if I do this , will this alter the percentage values by excluding the values ?

Basically I need to get the percetage of sales that are completed by month of the overall total. If I use the filter , I will be excluding the ones that havent sold which will affect my percentages.

[Arranafc19]

@Cmcmahan if I do this , will this alter the percentage values by excluding the values ?

Basically I need to get the percetage of sales that are completed by month of the overall total. If I use the filter , I will be excluding the ones that havent sold which will affect my percentages.

[Cmcmahan]

That depends on the measure you're using to calculate the percentage.  If you could share that with us, I can take a look to make sure it won't break.

[Arranafc19]

Hi @Cmcmahan , thanks for you help on this. I dont really want to share my actual data so I have created a mock up of my dataset.

So basically , at the moment I have a dataset like the above which lists the dealer , date sale started , date sale completed and month sold(datediff sql).

Basically what I am trying to achieved is that if I have a slicer to select a particular dealer and for the sale started , that I would pick up all cars where the sale start date was between my date range , and then I would show a breakdown per month sold of the percentage of cars that are sold each month. So it would show me per month , what percetage of my overall total that went on sale between my date range have sold. I used the percetnage of grand total option on the chart , however this wont work as once i exclude the cars that are not sold , my percentages per montha are just of those which have sold , not an overall total.

Hope this make sense , as I cant seem to find the solution to this issue 

[Cmcmahan]

That makes complete sense. I won't be able to give you specific help, since you can't share the measure, but I can show you the form it should take.  If you wanted to sanitize table/column names from your measure and share that, it would still be very useful.

So as I understand it, your date slicer is used to filter cars based on which month they arrive on the lot, and your graph shows a percentage of cars sold in each month, with unsold cars being blanks. I'm curious why you need to show a percentage instead of a count in the graph, but I can work with it.

PercentageSold = COUNTROWS(Table2) / CALCULATE(COUNTROWS(Table2), ALLEXCEPT(Table2, Table2[Date Sale Started], Table2[Dealer]))

The numerator counts the rows of the current table in context. The denominator does the same, but keeps the filters you have applied to Date Sale Started and Dealer, which are your slicers. 

[Arranafc19]

@Cmcmahan 

Thanks for that.

Yes basically I will have a slicer to filter by dealer and another slicer to only show dates that cars went on sale that are within a chosen date range.

I then need to show a percentage of cars per month that we sold as this allows me to map basically which months have the highest percentage of car sales from the month advertised to get an idea of how long it takes on average for a sale to go through 

[Cmcmahan]

Glad to help!

Did this solve your problem, or do you have further questions?

[Arranafc19]

Hi @Cmcmahan ,

I tried your suggesiton and it didnt do the job I needed it to.

What I am trying to achieve , I believe is not overly complicated , however it is proving to be difficult through Power Bi.

I have the above dataset and what I am trying to do is , select one dealer and a daterange on the sale date and from this I would create a chart which shows the below.

"Of all the cars that went on sale between my chosen dates and for my chosen dealer , how many have sold in month 1 , month 2, month 3 etc."

If you have any further suggestions on how to approach this I would be greatful for the assistance as I am all out of ideas 

[Cmcmahan]

So I used the sample data you provided, and created this: https://drive.google.com/open?id=1LNknORx1-h5nSisltzL5DtBUSO9smL3O

It doesn't look particularly great, since I have a very small data set, but I think it displays the information you want with the options you want.  Change up the visual as you see fit.

[Arranafc19]

Hi @Cmcmahan 

Thank you for the sample pbix file , very much appreciated.

I just had a look and it is very close to what I am looking far bar one issue.

The chart is showing a count of cars that have been sold by month where as I need to show the percentage.

For example , from the sample I provided , we have "O'Dwyers" , who between the date range , have 3 cars that went up for sale , with one selling in month 2. I need to show this as month 2 (33.3%) as only 1/3 of the cars sold that month.

For the "ABF Motors" , we had 4 cars go up for sale in that period, one sold in month 2 and the other in month 8.

I would need the grapth to show month 2 (25%) , month 8 (25%) and the other 50% would not be displayed on the report as these are not sold.

I am effectively trying to see from all the cars that went on sale between a particular time frame, what percetage sold in month 1 , month 2 , month 3 etc to see the highest percentage month it takes to sell

[Cmcmahan]

Sure. So instead of a car count, you would want to use a measure like this:

PercentageSold = COUNTROWS(DealerData)/CALCULATE(COUNTROWS(DealerData),ALLSELECTED(DealerData))

Go to the Modeling tab, select this measure and format it as a percentage. Then you can use this as the field instead of the Count of MonthOfSale.

What's happening is that we're using COUNTROWS(DealerData) to count all the cars currently in context, with the context being a specific MonthOfSale from the column of the chart, as well as Dealer and Date filters. 

Then it's dividing that by another count of rows, however I'm using CALCULATE syntax to change the context.  ALLSELECTED is a cool pre-set filter context that returns all rows that are used in the current visual.  The visual is trying to display all the data that respects your slicers, so this is an easy way to just count all of those rows quickly instead of mucking about with ALLEXCEPT or explicitly naming each field you want to keep in FILTER(ALL(table), field1=SELECTEDVALUE(field1)....) style syntax.

Hope this helps!

[Cmcmahan]

In that case, you'll want to set it up to explicitly remove some filters, instead of using ALLSELECTED.

PercentageSold = COUNTROWS(DealerData)/CALCULATE(COUNTROWS(DealerData),ALLEXCEPT(DealerData, DealerData[Dealer], DealerData[Date Sale Started]) )

[Arranafc19]

hi @Cmcmahan 

Thanks again for your help , but this didnt work for me.

The measure you gave me calculated the percentages right including null , but as soon as I either apply a filter to exclude null or I add a slicer to filter it out , the percentage values changes as it is excluding the null (unsold) cars from the calulcation. I basically need the percetnages to be calculated using the null values , but for the null value percentage to not appear on the chart. Not sure where to go with this.

[Arranafc19]

@Cmcmahan i am going to try apply this in the morning , and I will let you know if it worked for me. Very much appreciated