Custom table with dynamic data

[talha_ts]

I have a report with 4 slicers a date range and 3 others

I have got a visual that is linked to those slicers and it is working correctly.

I need a summary report in which i want to show custom data about count of category with and without filters but date applied.

e.g.

Category1  within applied filters   10

Category1  outside applied filters   1

Category2 within  applied filters     5

Category2 outside applied filters    0

I can only do within applied filters, how can i show both with filters and without filters data?

Is there a way I can do sql query and read the date filter from the slicer and do my own sql query? just a thought.

Thanks

Talha Ahmed

[v-yulgu-msft]

Hi @talha_ts,

Do you want the data displayed in visual not to be affected by some specific slicers? If so, you can turn off the interaction between slicers and the visual, please see Visualization interactions in a Power BI report

If this doesn't meet your requirement, please provide sample data and show us desired output. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[talha_ts]

Thanks for the response, yes I know about visual interactions - it would rather interact or not. Can in one table I can show the data about filtered and non filtered records?

If you see the example output i have provided in my original question, is that two rows in the table should come after the filter applied and two without. I can do that into two separate tables but the calculation won't be correct, is there a way to do that in one table?

If this is not possible, is there a  way i can run a dymamic query use the value from the slicers?

Hope it is much clearer now.

Thanks

[talha_ts]

And also to add to the above that the the count should be calculated based on the data within filter and outside filter.

For example:

Category 1 count within filter   = 10

Category 2 count within filter   = 5

Category 1 count outside filter = 0 

Category 2 count outside filter = 0

Total                                          = 15

Apply some filter using slicer

Category 1 count within filter   = 7

Category 2 count within filter   = 2

Category 1 count outside filter = 3 (10 - 7)

Category 2 count outside filter = 3 (5 - 2)

Total                                          = 15

Thanks

[v-yulgu-msft]

Hi @talha_ts,

You may need some measures to achieve such a result. For example, measures could be similar to:

Category 1 count within filter =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER ( ALLSELECTED ( Table ), Table[Category] = "Category1" )
)

Category 1 count outside filter =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER ( ALL ( Table ), Table[Category] = "Category1" )
)
    - [Category 1 count within filter]

For more advice, please provide sample data as suggested in this blog.

Best regards,

Yuliana Gu

[talha_ts]

Amazing, thats part of the solution, it is giving me the correct counts now. The only other problems are on how to display in the way I mentioned above along with totals. Sorry if this sounds a silly question.

Good that the counts are calculated and showing correct values. I want them to be displayed in rows along with some static values, please see attached image on what i actually need.

The Measures is what i can calculate now, what about the other bits and formation of table with totals and dynamic data?

Thanks for the response so far, much appreciated.

[talha_ts]

Any help on above?