Filters and Contexts - Table Visual Incorrect

[gbosley]

Just now running into this issue, or at least it's come to light.  I didn't realize the issue until I exported the data from a table and realized that it was missing customer details although the totals were correct in the visual itself.

One of my visuals compares current month customer revenue (October 2020) to prior year revenue for the same month last year(October 2019).  Revenue CY = Sum (Production Data [Revenue]).  Revenue PY = Calculate([Revenue CY], SAMEPERIODLASTYEAR(DateTable[Dates]).   My issue is that if there is no activity in October 2020 (Revenue CY), that client is excluded from the table even though there was revenue for October 2019. 

If the customer does not have any revenue in October 2020, that customer does not show up in the table individually since there is no revenue in current year.  For example, Client B has no activity in the production data table for the period selected (October 2020), but does have production data in the same period last year (October 2019).  In the visual itself, because the table is filtered for October 2020, the client does not show up in the table details but does in fact show up in the totals for Revenue LY (using SAMEPERIODLASTYEAR). 

It comes down to how the table is filtered and the context I'm using, but I don't know how to fix.

What I want to see is something similar to the following:

Client	Report Month	Revenue CY	Revenue PY	Revenue Change
Client A	October 2020	1,000	500	500
Client B	October 2020	0	1,000	(1,000)
Client C	October 2020	1,000	1,500	(500)
Totals		2,000	3,000	(1,000)

However, what i actually get because there is no data for Client B in current year is the following:

Client	Report Month	Revenue CY	Revenue PY	Revenue Change
Client A	October 2020	1,000	500	500
Client C	October 2020	1,000	1,500	(500)
Totals		2,000	3,000	(1,000)

Client B is left off the table but included still in the totals.  WIth the vast number of clients it wasn't evident until I exported to Excel and summed the Revenue CY and noted that individually they did not total to the actual total.

I'm thinking it has to do with how I am calculating Revenue CY and not using approrpiate filters. 

What am I missing?  I'm obviously also missing an important learning aspect here as it relates to filtering and contexts, so I would appreciate any additional resources on where I could advance my knowledge.  Thank you!

Best, 

Geoff

[daxer-almighty]

I think the problem could be your model. Please stick religiously to the star schema if you want to avoid pitfalls and never put columns from your fact tables in visuals. Slicing and dicing only via dimensions is allowed. Here's the mandatory reading: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema. Please note that clients should reside in a dimension of its own. Only then will you be able to put them in the matrix and see them all if at least one of the measures returns a non-BLANK value. If you start sourcing them from the fact table, you've already lost your case.

[gbosley]

@wdx223_Daniel thank you very much for the response.  This made so much sense in that if there was revenue last year then at a minimum give that client 0 for current year so they would show.  However, it didn't change the visual and still excludes those with 0 in current year.  I thought maybe there should be a "<>0" in the IF statement, but that was the same result.  Tried adding 1,000 instead of 0, but all that did was change the amounts for certain clients but still excluded those with 0 in current year and <> 0 in prior year.  My current formula looks like:

Book Revenue =

IF (

CALCULATE (

SUM ( 'Production Data'[AccrualBalanceCalc] ),

SAMEPERIODLASTYEAR ( 'Date Table'[Date] ),

'Pr/Br Comm'[ProductionCreditPercent] = 100

) <> 0,

CALCULATE (

SUM ( 'Production Data'[AccrualBalanceCalc] ),

'Pr/Br Comm'[ProductionCreditPercent] = 100

) + 0,

CALCULATE (

SUM ( 'Production Data'[AccrualBalanceCalc] ),

'Pr/Br Comm'[ProductionCreditPercent] = 100

)

)

The only difference is that there is also a separate filter on the calculate to attribute the revenue to a particular sales team member, so i have in addition to the reporting period a selected sales team member.  i wouldn't think that would impact this but I suppose it could.  I will continue to try and tweak the IF statement as that seems like the right answer here.  Totals remain correct.

Thank you!

[wdx223_Daniel]

@gbosley if you want show all customers whatever,then it might be just adding +0 on your original measure before changing as my advice

Book revenue=CALCULATE (

SUM ( 'Production Data'[AccrualBalanceCalc] ),

'Pr/Br Comm'[ProductionCreditPercent] = 100

)+0

[gbosley]

@wdx223_Daniel thank you again, but unfortunately that doesn't work either, it just gives CY and PY $0.  I have selected one particular customer that has no revenue in October 2020 but does in October 2019.  You can see by the visual below (filtered to October 2020 and October 2019) that in October 2019 there is CY Revenue and PY Revenue (October 2018), but for October 2020, there is no revenue for each although we know there was $5,760 in October 2019.  Not sure why, but it appears because there is no revenue/production detail in October 2020, and even though we include a zero added in to give it a "figure", the time intelligence can't pick up October 2019 revenue simply because nothing exists in October 2020 (pure guess at this point).  I would have thought this was going to be easier!  Now I have to rethink all my models in that they are missing critical year-over-year client revenue analysis details.

[wdx223_Daniel]

@gbosley can add a if function, such as

Revenue CY = IF(Calculation(Sum (Production Data [Revenue]),sameperiodlastyear (datetable[date])), Sum (Production Data [Revenue])+0,Sum (Production Data [Revenue]))