Counting Number of store vs Number of products- DAX

[Mkrishna]

Hello Community Members,

I am unable to share the data due to  sensitivity.  I want to make a bar chart which shows number of products  vs number of store.

I have a fact table called Fact. The table has brand, store number, brand and product columns. I also have a dimension table called store number with column store number. The dimension brand has column brand and , dimension product has column product.
The product in brand  can vary. Some brand may have just 2 product, some may have 16 product and so on. Based on the brand the user choose in slicer I want o count

1. How store has atleast 1 product
2. How many store has atleast 2 product

3. How many stores has 3 prodcut

4. how many store has 4 product
5. This goes on till the number of distinct product

And I want to show this in bar chart. I have tried may time but I am unable to move forward due to limited knowledge of dax and power bi.
The output should look like this

The table shows that 15 stores has atleast 1 product for a brand, 10 store has 2 products (can be any mix), ,7 store has 3 products and so on. This number of product should change dynamically with the total distinct count of proudct for each brand. The same is shown in bar chart.


Thank you member for the help.

[mszakalinski]

@Ashish_Mathur would you share your solutions? The link is empty

[Ashish_Mathur]

I do not have that file now.  Share some data, explain the question and show the expected result.

[Mkrishna]

Hi Ashish

The picture of the dataset is as follows. The community does not allow me to up

load the dataset so I am sending the pic.

If I want to find the solution for brand Dhoni then I should get this answer

[v-zhangti · Accepted solution]

Hi, @Mkrishna 

You can try the following methods.

Column:

Count Product = CALCULATE(COUNT('Table'[Product]),ALLEXCEPT('Table','Table'[Store Number],'Table'[Brand]))

New Table:

New table = VALUES('Table'[Count Product])

Measure:

Number of store =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Store Number] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[Brand] ),
        [Count Product] >= SELECTEDVALUE ( 'New table'[Count Product] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Ashish_Mathur]

Hi,

I have solved a similar problem here.  Please apply the same step/formulas to your data.

[Ashish_Mathur]

Hi,

Share a dummy dataset to work with.