Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dennis_9
New Member

How can I calculate the number of stores that meet a certain condition by month?

Hi All,

 

dennis_9_0-1718228834230.png

I want to calculate the number of stores and show in a matrix like above. the Year and Month Names are in the "Calendar" table. And HGO rates are in the table (as measures) named "Target" that i created. Finally, i have only Store Code column in the Store table. So, How can I find the number of stores that meet a certain condition by month? 

 

Thank you

1 ACCEPTED SOLUTION
dennis_9
New Member

Hi All,

I've been trying to find this formula for a week 😀 I'm sharing it if anyone needs it. The highlighted parts were a filter that I had to write to get the right results. You don't have to write anything there or you can write your own filters.

dennis_9_0-1718361402417.pngdennis_9_1-1718361426477.pngdennis_9_3-1718361515533.pngdennis_9_4-1718361565057.png

And finally,

(I had already created common columns for the table named "Values" ​​and the "Calendar" table and created their relationships. That's why I didn't mention it above.)

This is my Values table:

dennis_9_5-1718361966863.png

 

dennis_9_6-1718362162324.png

 

I placed it in the matrix table like this:

dennis_9_8-1718362554465.png

 

 

 

 

 

View solution in original post

5 REPLIES 5
dennis_9
New Member

Hi All,

I've been trying to find this formula for a week 😀 I'm sharing it if anyone needs it. The highlighted parts were a filter that I had to write to get the right results. You don't have to write anything there or you can write your own filters.

dennis_9_0-1718361402417.pngdennis_9_1-1718361426477.pngdennis_9_3-1718361515533.pngdennis_9_4-1718361565057.png

And finally,

(I had already created common columns for the table named "Values" ​​and the "Calendar" table and created their relationships. That's why I didn't mention it above.)

This is my Values table:

dennis_9_5-1718361966863.png

 

dennis_9_6-1718362162324.png

 

I placed it in the matrix table like this:

dennis_9_8-1718362554465.png

 

 

 

 

 

v-heq-msft
Community Support
Community Support

Hi @dennis_9 ,
Based on your description, the date table is a standalone table, you can think of this table as a dimension table and make sure that this table has a relationship with the date field of the target so that you can create a slicer and keep the month field for filtering purposes

vheqmsft_0-1718243755551.png

vheqmsft_1-1718243795066.png

For specific data and visualization creation, you can check out the following pbix file

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Hi, @v-heq-msft ,

I created the table (Home -> Enter data) named "Target" and it only contains measures. Therefore, i cannot add any columns to "Target" from Table View tab. 

dennis_9_1-1718260903506.png

In addition,  i only have about the stores Store Code column in the "Store" table. 

aduguid
Solution Sage
Solution Sage

Can you post some example data as well as the conditions that create each grouping, e.g. "HGO < %85"?

Hi @aduguid , here are some examples 

dennis_9_0-1718260205830.png

Thank you

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors