I have a calculated field where I am doing a COUNT of rows based on a condition, using the SUM(IF) approach, i.e.
SUM( IF [Sub-Category] = "Chairs" THEN 1 ELSE 0 END )
If I put this in to a table with Order Date on the Rows and Ship Date in the Columns, I get the value 5 for January 2022:
When I click this cell and use the View Data option, and then the Full Data tab, I am expecting to see 5 rows here, but instead I see the total amount, 42.
Why is this? And is there something I can do to ensure it only shows the 5 Chair rows?
Thank you!
Hi @John McPhillips - yes and no. Your calc is a conditional sum rather than a filter. So you have rows where the sum is 0 and it's quite valid for those to be in the underlying data. If you filter the rows out (sub-category = Chairs) the you should get matching underlying data. But judging by your view you don't want them filtered out as you want another measure showing more than just chairs? You could use a dashboard and some actions to let people drill into the detail list (with a filter on it) and then they get the data from that list. Ta, Steve.
