Forum Discussion
Not Displaying Values on Chart
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
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!
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]) )
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.