ALL Function Not Working

[afelber]

Hello,

I am trying to create a a measure to use in a graph that is not impacted by a slicer filter called 'listing number' on the report. The measure I created is as follows:

25th Percentile Number Bookings = CALCULATE(CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER('Metrics by Listing','Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0)),ALL('Metrics by Listing'[Listing Number]))

I used the measure in a line graph where the month is on the x-axis and the number of bookings on the y-axis. However, when I change the listing number slicer, the graph changes. 

[rsapkota]

Did you solve this issue? I have a similar problem where I have a Customer slicer on the page but want to get a percentage value based on the total Value regardless of the customer. The All function does not seem to work in this case. It filters the values by the customer selected in the slicer. 

The query I am using to calculate the denominator is as follows: 

Spend YTD = CALCULATE(Sum('Invoices'[Revenue]), ALL(Invoice_Customers[Customer Name]), Filter(Invoices, [IsYTD]))

The IsYTD measure in Invoices table returns true or false. I am using this to filter out all invoices that are not in this YTD. 

The calculation used to work when the IsYTD was a column and I did not have to use a Filter. Since there are a lot of calculations behind the IsYTD filter, I had to change it to a measure. It has not worked since. Do I need to do something extra when I use a Filter in the measure for All to work? 

[v-qiuyu-msft]

Hi @afelber,

Please modify the DAX like below:

25th Percentile Number Bookings = CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER(ALL('Metrics by Listing'),'Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0))

Best Regards,
Qiuyun Yyu

[afelber]

I do not want all filters removed though, just for the column 'listing number' in the table? 

[v-qiuyu-msft]

Hi @afelber,

Please don't use ALL() function,

25th Percentile Number Bookings = CALCULATE(PERCENTILE.INC('Metrics by Listing'[Number of Bookings Listing], 0.25),FILTER('Metrics by Listing','Metrics by Listing'[Owner]<=10 && 'Metrics by Listing'[Hidden]=0 && 'Metrics by Listing'[StartMiddle]=0))

Then select the slicer visual, enable Edit Interactions, click the NONE for chart. See: Visualization interactions in a Power BI report.

Best Regards,
Qiuyun Yu

[afelber]

Thanks Qiuyun. I have used this interaction functionality before however it does not work for what I need because I need one line in the graph to respond to the slicer. 

I will provide a bit more detail on what I am trying to accomplish. Suppose I have a table that has a four colunms: unique ID, city of the ID, state of the ID, month, and sum of revenue for that unique ID in the month. 

I want to create a line graph where the x-axis is month and y-axis is sum of revenue with 2 lines in the same graph. One line should only show the sum of revenue by month for a specific ID selected in the slicer and the other should show the 25th percentile of revenue for all IDs (i.e. ignore the slicer) so that a user can compare that values for the specific ID to the 25th percentile. I also need to be able to filter the 25th percentile by a city slicer and state slicer. Is it not possible to build the 25th percentile measure to accomplish this?