Computed total based on measures does not respect page filters

[bradlee]

I am reporting on subscriptions.  The data is organized in a parent/child relationship.   The parent is a Summary table, with the children as Orders.  The key between the tables is the date.

Originally, this reported static results by date with no filters.  The summary data came pre-computed from SQL Server.  Now I want to allow the user the ability to select various filters.  I'm changing the PowerBI report to support this new requirement.  The data looks like this:

A relationship is setup between the two tables.  This is pretty much what the report looks like also, except with date drill down enabled.  I've defined a few measures in the parent Summary table as follows:

Measures
ComputeNew = CALCULATE(count('Detail'[OrderNo]), 'Detail'[TransactionType] = "N")
ComputeRenew = CALCULATE(count('Detail'[OrderNo]), 'Detail'[TransactionType] = "R")
ComputeCancelled = CALCULATE(count('Detail'[OrderNo]), 'Detail'[TransactionType] = "C")

These work fine and provide the correct numbers as the report is filterd.  However, for the "Expired" column, I need to look for last year's subscriptions that expire on the specific date.  As far as I can tell, I cannot do this with a measure.  I created a computed column in the summary table as follows:

ComputeExpired = 
var CurrentDate = 'Summary'[Date]
return COUNTX(
     FILTER('Detail',
         'Detail'[RenewalDate] = CurrentDate
         && 'BI PIFDetailPsdn'[TransactionType] IN {"N", "R"}
     ),
     'Detail'[OrderNo]
)

Again, this produces the expected results.  The problem I am running into is when creating a Computed Total column.  The computed total is defined as:

Total = [ComputedNew] + [ComputedRenew] - [ComputedCancelled] - [ComputedExpired]

The total is correct as long as no other filters are active.  If a filter is active for the subscriptions table, the total ignores that filter.  It does not add up the value of the filtered measures.

[bradlee]

After more investigation, the computed total column does not respect the page filters.  For example, if the subscriptions are filtered by a category, the summary measures update with the correct totals.  However, the computed total shows the total of unfiltered results.  This seems to be a bug in PowerBI.

[v-yingjl]

Hi @bradlee ,

Based on your description, seems like there are more than three tables in your dataset which have been quoted in the measure formula.

If it is just calculation issue, not think it could be an bug in power bi, could you please sharing more details like some sanple data and expected output for further discussion?

Best Regards,

Yingjie Li