Forum Discussion
items with no value still showing up
I am looking at sales data, each line item in data table is a product with a category. When I look at 2020 vs 2019 it still pulls in old categories that have not been sold since 2018 so I have all these rows in my table with no values. I don't have show items with no data checked. Any ideas?
11 Replies
- parry2k
Super User
Anonymous what are your measures and how you are visualizing it? Maybe it is trying to show 2019 vs 2018 the way you are trying to visualize the data.
I have a matrix with category in the rows and then measures in the values. It is filted on 2020 and my measures are like this:
current year=sum(net sales)
last year=IF (HASONEVALUE ( '4-4-5 Calendar'[4-4-5 Year] ),CALCULATE (SUM (net sales),FILTER (ALL ( '4-4-5 Calendar' ),'4-4-5 Calendar'[4-4-5 Year] = VALUES ( '4-4-5 Calendar'[4-4-5 Year] ) - 1&& CONTAINS(VALUES ( '4-4-5 Calendar'[DayOfYear] ),'4-4-5 Calendar'[DayOfYear],'4-4-5 Calendar'[DayOfYear] ))),BLANK ())
Because I am using a retail calendar. All my data flows correctly but if I scroll down on my chart i have all these categories with no data.
- Pragati11
HI Anonymous ,
Please share screesnhots around your issue. It's hard to visualise what you are trying to convey here.
Thanks,
Pragati
- Greg_Deckler
Community Champion
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - amitchandak
Anonymous , Hop you do not have overall measure, even this is filtered
refer
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))Refer
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
- amitchandak
Anonymous , Just saw the last update, have added +0 ?
v-lili6-msftCommunity Support
HI Anonymous
From the screenshot, it likes you enable show items with no data for categroy field in matrix, could you please share your sample pbix file for us to have a test, there should be something wrong in other.
Regards,
Lin
here is a test file
https://www.dropbox.com/s/43azmuya0scr0ld/test%20report.pbix?dl=0
- v-lili6-msft
hi Anonymous
The sample file is a live connection to power bi dataset, so we could not open it.
Regards,
Lin
