Summarize filtered table

[myonaiz]

Hi, I have a table Like the following

App Id	Date	Location	Count
1	2020-1-1	USA	2
1	2020-1-2	USA	3
2	2020-1-1	China	5
2	2020-1-3	China	10
3	2020-1-2	USA	4

I created a summarized table using the following DAX Expression

SummarizedTable = SUMMARIZE(Table, Table[App Id], "CountSummarized", SUM(Table[Count])) 

I also created a graph that renders a histogram of the number of app Ids per each count bucket. I do not know if it is possible or no but I want to add the Date and Location columns above as filters but not summarization dimensions. in other words I want the data that gets fed to the summarization DAX expression to change based on filters (slicers) on Location and Date. any idea how I can do that?


Thanks a lot in advance 

[Ashish_Mathur · Accepted solution]

Hi,

Create a Table visual and drag App Id there.  Write this measure

Measure = SUM(Data[Count])

Hope this helps.

[Ashish_Mathur]

Hi,

Please explain the business context and for the data that you shared in the original post, please show the expected result.

[myonaiz]

I am sorry I do not understand what you mean by the bussiness context.

but for the table I shared here is the expected result:

original table for reference:

App Id	Date	Location	Count
1	2020-1-1	USA	2
1	2020-1-2	USA	3
2	2020-1-1	China	5
2	2020-1-3	China	10
3	2020-1-2	USA	4

No filters applied the data should be: (just summarizing all rows by app id and summing the count)

App Id	Count
1	5
2	15
3	4

with the user for example choosing USA: (just summarizing all rows in location USA)

App Id	Count
1	5
3	4

with the user for example choosing date 2020-1-1: (just summarizing all rows that happened on 2020-1-1)

App Id	Count
1	2
2	5

[amitchandak · Accepted solution]

The simple aggregation will do that. No need to use summarize. Other thing you should do is create a Date Calendar dimension and join it with your tables. You can also have dimensions for USA and ID as per need. Try to have a star schema

Refer : https://docs.microsoft.com/en-us/power-bi/guidance/

you can simply use sum in Visual or create a measure

Counts = sum(Table[Count])

I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-y...

[myonaiz]

Hi, your solution is what I want but I can't use it because I need to render this measure in a graph. trying to do that on a simple bar graph failed in power bi. when I change the measure to a column my problem is back which makes sense. the measure summarizes the data itself. but unfourtunatly I can't use this measure, any other alternative, please?

[Ashish_Mathur · Accepted solution]

Hi,

Create a Table visual and drag App Id there.  Write this measure

Measure = SUM(Data[Count])

Hope this helps.