Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gbosley
Frequent Visitor

Filters and Contexts - Table Visual Incorrect

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:

 

ClientReport MonthRevenue CYRevenue PYRevenue Change
Client AOctober 20201,000500500
Client BOctober 202001,000(1,000)
Client COctober 20201,0001,500(500)
Totals 2,0003,000(1,000)

 

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

 

ClientReport MonthRevenue CYRevenue PYRevenue Change
Client AOctober 20201,000500500
Client COctober 20201,0001,500(500)
Totals 2,0003,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

5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

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
Frequent Visitor

@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!

@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

@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.

PBI Capture1.PNG

wdx223_Daniel
Super User
Super User

@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]))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.