Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Arashi
Frequent Visitor

Count based on data of other column and sum up

‎03-31-2022 08:19 AM

Hello,

 

I want to get the number of stores by each year and by each type of stores based on the sales column.

If it is > 0, I should be counted.

 

This should be the output result:

 

Arashi_0-1648739591425.png

 

I am using DISTINCCOUNT and it works well at store level, but the aggregation at type of stores and total is incorrect because it counts stores even with zero sales.

Below the sample dataset.

 

CategoryZoneStoreExtentionType of StoreUnitsYear
Category5rear3254136,6E02022
Category5rear2052102,06C2172022
Category5rear4534168,07E02022
Category5rear2067117C1742022
Category5rear7866116C2262022
Category5rear250B02022
Category5rear650D02022
Category4rear6756118,92A6392022
Category4rear110B02022
Category4rear112133,67D02022
Category4entrance3254279,96E02022
Category4entrance2052351,11C3132022
Category4entrance4534369,54E02022
Category4entrance2067322C3862022
Category4entrance7866439C3382022
Category4entrance25313,48B5032022
Category4entrance65192,86D2202022
Category3entrance6756355,63A9112022
Category3entrance11337,65B8382022
Category3entrance112281,71D2252022
Category3rear3254136,6E02022
Category3rear2052102,06C1962022
Category3rear4534168,07E02022
Category3rear2067117C1362022
Category3rear7866116C1322022
Category3rear250B72022
Category2rear650D02022
Category2rear6756118,92A3132022
Category2rear110B02022
Category2rear112133,67D02022
Category2entrance3254279,96E02022
Category2entrance2052351,11C4892022
Category2entrance4534369,54E02022
Category2entrance2067322C5452022
Category2entrance7866439C6992022
Category2entrance25313,48B13192022
Category2entrance65192,86D3462022
Category1entrance6756355,63A17172022
Category1entrance11337,65B18602022
Category1entrance112281,71D3662022
Category5rear3254136,6E1202021
Category5rear2052102,06C02021
Category5rear4534168,07E5412021
Category5rear2067117C1862021
Category5rear7866116C5802021
Category5rear250B9152021
Category5rear650D282021
Category4rear6756118,92A1342021
Category4rear110B02021
Category4rear112133,67D1712021
Category4entrance3254279,96E2822021
Category4entrance2052351,11C02021
Category4entrance4534369,54E5872021
Category4entrance2067322C7722021
Category4entrance7866439C822021
Category4entrance25313,48B4302021
Category4entrance65192,86D7782021
Category3entrance6756355,63A6992021
Category3entrance11337,65B02021
Category3entrance112281,71D6142021
Category3rear3254136,6E4982021
Category3rear2052102,06C02021
Category3rear4534168,07E8752021
Category3rear2067117C2782021
Category3rear7866116C9512021
Category3rear250B4842021
Category2rear650D1192021
Category2rear6756118,92A2472021
Category2rear110B02021
Category2rear112133,67D9422021
Category2entrance3254279,96E9102021
Category2entrance2052351,11C02021
Category2entrance4534369,54E412021
Category2entrance2067322C442021
Category2entrance7866439C7372021
Category2entrance25313,48B6582021
Category2entrance65192,86D1962021
Category1entrance6756355,63A7302021
Category1entrance11337,65B02021
Category1entrance112281,71D1812021

 

It should also be considered that when I slice by zone type some stores values might become zero as well.

Your help will be much appreciated.

Message 1 of 3
310 Views
0
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

‎03-31-2022 08:53 AM

I think the below should work as a measure

Num stores = CALCULATE( DISTINCTCOUNT( Stores[Store]), Stores[Units] > 0)

View solution in original post

Message 2 of 3
276 Views
0
2 REPLIES 2
johnt75
Super User
Super User

‎03-31-2022 08:53 AM

I think the below should work as a measure

Num stores = CALCULATE( DISTINCTCOUNT( Stores[Store]), Stores[Units] > 0)
Message 2 of 3
277 Views
0
Arashi
Frequent Visitor
In response to johnt75

‎03-31-2022 09:18 AM

Hello, this is not working because in the part in the part "Stores[Units]" I need to input another measure or a calculation because I need to filter by the sales for each year.  

 

Year cannot go into the matrix column headers in order to filter as I have many other values.

I need to create two separate measures for each year and inside them filter for each year sales accordingly.

 

Thanks!

Message 3 of 3
272 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All