How to create a grouped table from an existing table that moves with drop down filters

[FOXYBARK]

Hi all. 

My questions is very elementary, I think. I only want to create a separate grouped table (grouped by date) with a count, that moves with drop down filters that are already in my viz. Seems when I use the summarize function, the numbers don't move at all. I simple keep getting the entire row totals. 

tblData = SUMMARIZE(Badge, Badge[BADGE_SCAN_DATE], "Countx", CALCULATE(COUNT(Badge[ACCESS_BADGE_NUMBER])))

Is SUMMARIZE supposed to be "locked"? Is there a better way to create my table so when I include metrics from the table into my viz, it adjust to the filters in place?

Thanks, LB

[Whitewater100]

Hi:

Usually you might want to create a Summarize table to aggregate values for a summary, but most often it is a virtual table as part of a different measure, as a helper table. I'm not sure if I understand your question 100%. If you have data model and are summarizing your Fact Table and other tables with dimesions it should maintain lineage. 

When I look at your example, I'm thinking you have one table? You best split it out to facts and dimensions to be able to use Power BI full analytical power. I'll attach a model so you can see.

If you do want a table to filter I'll put an example for getting Sales by City. City is part of a dimension table called Regions and [Total Sales] is derived from the fact table.

Table Filtered = FILTER(

                            SUMMARIZE(
                                                     Regions[City],
                          “Total City Sales”, [Total Sales]
),

[Total City sales] > 10000
)

Link to example model:

https://drive.google.com/file/d/1QgEO4YwQQh2lqtXk1hnwQYVBuv6BbKcS/view?usp=sharing

https://drive.google.com/file/d/1QgEO4YwQQh2lqtXk1hnwQYVBuv6BbKcS/view?usp=sharing 

I hope this helps!

[FOXYBARK]

HI. 

Thank you for the reply. Yes, I have 1 table that closely resembles this... but much bigger. 

I want to group by badge date and have the count of badge # per date. when I place the grouped table on the viz, I want to be able to filter by Building Name and HR Dept. Simple in concept but I'm new to PBI. 

[Whitewater100 · Accepted solution]

Hi:

Please see attached file for results.

https://drive.google.com/file/d/1ubjkS2ZWQREEaGdgiOHSSR9PHQf_tfJU/view?usp=sharing 

I hope you can mark as solution.

[FOXYBARK]

Thank you. I will test this and see if it works for my objective. 

[Whitewater100]

Hi:

If you have a representative example of this data in excel? Feel free to change any sensiive data and I'll try show you what to do. Thanks/...

[Whitewater100]

Hold. I didn't see. I'll try something now.