Forum Discussion
Adding counts together from different columns with different filters
Hi,
Sorry if I'm being rather silly but this is something I really struggle with in PowerBI as I've had no training in the coding language used and don't particularly use measures unless I'm shown how.
I am trying to add together counts based on two filters from one column say Count A (filter 1 & 2 applied) plus Count B from a second column (filter 1 applied) to get a total figure - this seems easy in my head/using other tools but I can't seem to crack it in PowerBI Desktop.
Is there a simple way to calculate this please?
Hello! You would create measures to handle this. Let's say you have a table with store numbers, sku sold, date, units sold and $ sold, but you want to know how many skus any given store sold for category A and B and you want to know how many for C & D, and you want to know the total of those.
Ideally, you will have a dim_store_list table that has all the details about the store numbers (address, city, state, phone, etc), a dim_date_table (date, month number, month name, quarter, week number, etc.), a dim_products table (sku number, sku description, category, brand, sub-category, sub-brand, etc). These tables would be joined to the fact_sales table I mentioned in the first paragraph.
You could then make a measure that would be for sales (Sales = SUM('YourTable'[Sales]), then you could create the first filtered measure; for my example, I just want to see the sum of sales for Bikes and Accessories
Sales (Bikes and Accessories) =CALCULATE([Sales],'Product'[Category] IN { "Accessories", "Bikes" })You would do the same for the second set of conditions you have, then you would make a measure to add these two measures together.
1 Reply
Hello! You would create measures to handle this. Let's say you have a table with store numbers, sku sold, date, units sold and $ sold, but you want to know how many skus any given store sold for category A and B and you want to know how many for C & D, and you want to know the total of those.
Ideally, you will have a dim_store_list table that has all the details about the store numbers (address, city, state, phone, etc), a dim_date_table (date, month number, month name, quarter, week number, etc.), a dim_products table (sku number, sku description, category, brand, sub-category, sub-brand, etc). These tables would be joined to the fact_sales table I mentioned in the first paragraph.
You could then make a measure that would be for sales (Sales = SUM('YourTable'[Sales]), then you could create the first filtered measure; for my example, I just want to see the sum of sales for Bikes and Accessories
Sales (Bikes and Accessories) =CALCULATE([Sales],'Product'[Category] IN { "Accessories", "Bikes" })You would do the same for the second set of conditions you have, then you would make a measure to add these two measures together.