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

[dennis_9]

Hi All,

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

[dennis_9 · Accepted solution]

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.

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:

I placed it in the matrix table like this:

[Anonymous]

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

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

[dennis_9]

Hi, @Anonymous ,

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. 

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

[aduguid]

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

[dennis_9]

Hi @aduguid , here are some examples 

Thank you