COUNTROWS with a slicer and treemap

[brokencornets]

Hello

I'm trying to create a report showing jobs raised by employees, but I'm having some issues with some measures I've created not showing correct results when clicking on the tree map.

The first image below, after clicking on the bar graph, shows the correct results. But when clicking on the tree map the figures change slightly - and it brings back slightly different results when clicking on each detail section for each employee.

Just wondering if anyone can explain why that would be happening and what I'm doing wrong? It seems to be linked to the Date Reported slicer, because when I change this to all dates the figures all come out okay.

Thanks in advance

bc

 

[Stachu]

Isn't this due to the different scope being filtered?
on the Treemap you filter Employee8 & W, on bar chart you filter just Employee8

[brokencornets]

Yes, quite possibly Stachu - although in that case shouldn't we expect U, E & M to all show zero?

I'm struggling to understand where the 392 Us are coming from...

[Stachu]

no, when you specify the filtere in calculate:
Sheet1[Priority]="E"
it's equivalent of

FILTER(ALL(Sheet1[Priority]),Sheet1[Priority]="E")
so it overwirtes fitler context on Sheet1[Priority] that comes from slicers, visuals, etc.
are the source of Employee8 the same on all visuals/measures? as well as source of E,W,etc.?

[brokencornets]

Hi Stachu

Apologies, but you have me confused - if you're saying it should bring back ALL E items regardless of employee or date, then that's not happening.

So when I click on Employee8 and detail of W, it does being back the correct number in the W measure for that employee and for the specified timeframe. However, it changes the E, U, M and O measure results. Similarly if I click on the E, it changes the E measure back to the correct figure of 394, but reduces the other figures:

I could understand if it turned the other measures to 0, but I can't work out what it's calculating to come up with these random, slightly smaller, numbers.

[Stachu]

what I mean is following:

when you click W on the visual you create filter context on the Sheet1[Priority] = "W".

when you hardcode that filter to "E" in CALCULATE then, regardless of your selection, it will always overwrite it with "E" (even if the visual context is "W")
all other dimensions create filter contexts on different columns, therefore are not overwriten - so employee, time etc. filter normally

[brokencornets]

Thanks for bearing with me Stachu.

I get that it'll always overwrite the W with E, and that's how I want that measure to behave. I want it so that, until I change the employee or time filter, 394 always remains in E. What I don't get is why, when I click on W, E changes to 393. Why is it only failing to count one specific instance? Surely it can either still count ALL of the Es, or none?

In answer to your earlier question, yes, the source of all visuals is the same table.

[Stachu]

may be a long shot but - I think the issue is with COUNT of Raised in the Treemap. it excludes blanks by default, which may cause a difference
so I would change the measure in Treemap to something like

COUNT(Sheet1[Raised])+COUNTBLANK(Sheet1[Raised])

[brokencornets]

The figures in the treemap are always correct - it's the individual E, U, W, M & O measures in the bottom right that are causing me headaches 😞

[Stachu]

then I would modify the formula for E, W, etc. to match that from treemap

E = CALCULATE(COUNT(Sheet1[Raised by]),Sheet1[Priority]="E")
current formula foe E,W, etc. is counting rows in table, treemap is counting entries in a column - they may be inconsistent

[brokencornets]

Thanks for the suggestion, but unfortunately changing the measures as you suggested brings back the same results

I've changed

E = CALCULATE(COUNTROWS(Sheet1),Sheet1[Priority]="E")

to

E = CALCULATE(COUNT(Sheet1[Raised by]),Sheet1[Priority]="E")

but it brings back the exact same results with the same problem.

Is it just that too many things are filtered? When I change the date slider to include ALL dates, the results on the cards always match the treemap and don't change regardless of where I click, which is exactly how it should be.

[Stachu]

then I still think the measure on the treemap is inconsistent with the visuals. Whatis the syntax there?
alternatively there is some additonal visual filter applied to one of the visuals, or the interactions are changed from the default behaviour

[brokencornets]

The treemap is created using the standard visualisation 'out of the box' and the numbers in it are definitely correct - see comparison with Pivot Table for the same data. You can see below that the figures in the treemap match the pivot table, but when I click on the E on the treemap it changes the figures in the cards on the right.

I know I'm doing something wrong, it just seems to be so hard pinpoint what it is because the results are so unusual.

[brokencornets]

Still struggling with this issue - any other experts who could offer assistance?

Thanks in advance!