Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table with "Device" and "Users" column shown below. The sum of users for each device are displayed. When I apply filter for Users column >= 220,000 then "Other" device disappears but then when I add the Users column to a slicer then the the table is displaying wrong values.
The filter Users is applied below in the filter pane and the results are accurate.
The same slicer is applied on the page but results are not accurate as shown below it is filtering out results not >=220,000 also the mobile users number is wrong.
Can someone tell me why the slicer is giving wrong values but filter is giving correct results.
Thanks in Advance
Neelofar.
I agree with @moizsherwani. Another option is to aggregate the sum of users by device into a calculated table first. Then use columns from this new table in the table visual and slicer.
You can create a calculated table with below code
Table 2 = SUMMARIZE('Table','Table'[Device],"Users",SUM('Table'[Users]))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @neelofarshama when you are applying a filter to the table, you are actually applying a filter to the summarized view, i.e. when you filter >=220,000 the visual looks at all the aggregated data by device and filters out where it "sees" the "total" to be greater than 220,000. If on the other hand you use the "user" column as a slicer then the slicer filters the underlying data to exclue where any of the individual rows for a device is greater than 220,000. In this case your snippet shows that only rows for mobile have values (individually) >=220,000 whereas rows for other devices do not. You can also see that the total for the mobile is different to what it was when you used it as a visual filter and it is the same reason, rows where the value for users was <220,000 were excluded. Let me know if this makes sense
Regards,
Moiz
If this post helps, please "Accept" it as Solution to help other members find it.
Thank you @moiz for the reply it really helped but can you suggest any other way of getting the requirement with the slicer
The easiest way which is probably not the best (because you won't be able to filter the data using any other field) is to create a calculated column that has a total of the DEVICE on each row if Desktop total is 26,083,473 then it shows up in every row where the DEVICE = desktop. The way to do that would be
TotalUSers =
CALCULATE (
SUM ( 'Table'[Users] ),
FILTER ( 'Table', 'Table'[Device] = EARLIER ( 'Table'[Device] ) )
)
The better way which maybe too complex/advanced to simply explain here would be to add a "user count table" which is a disconnected table (using a GENERATESERIES) and then creating a measure for sum of users which references this disconnected table (something along the lines of)
UserCountTable =
GENERATESERIES ( 0, SUM ( 'Table'[Users] ), 1 )
// use this as a slicer
FilteredDevices =
CALCULATE (
SUM ( 'Table'[Users] ),
FILTER (
VALUES ( 'Table'[Device] ),
SUM ( 'Table'[Users] ) >= MIN ( UserCountTable[Value] )
)
)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |