How to use "Countdistinct" in this scenario?

[Trooz]

Hello friends,

I'm still a beginner and I was performing an assessment which asked me to use "COUNTDISTINCT" to get the number of stores each product was sold in.

This is the full table in the data view: 

And here is the final solution:

Can you help me understand how to get the number of stores sold in to look as same as we see in the solution ?

Thanks in advance!

[harshnathani · Accepted solution]

Hi @Trooz ,

Please use below measures:

Number of Store Sold In =

CALCULATE(DISTINCTCOUNT(Data12[CentreId]),ALLEXCEPT(Data12,Data12[ProductName],Data12[CentreId]))

Quantity Sold =

CALCULATE(SUMX(Data12,Data12[Quantity]),ALLEXCEPT(Data12,Data12[ProductName],Data12[CentreId]))

Regards,

Harsh Nathani

[Anonymous · Accepted solution]

// You should have a dimension
// Store in your model and a Sales
// fact table. Store joins on StoreID
// to the fact table.

// This measure gives you the number
// of different stores that ANY of 
// the products currently in scope
// has been sold. If only one product
// is in scope you'll get the number
// of stores that the product has 
// been sold in.
[# Distinct Stores] =
	DISTINCTCOUNT( Sales[StoreId] )
	
// If you now put your products
// on rows and drop this measure
// you'll see the number of different stores
// the product has been sold in. This
// also works with all slicers you
// could think of.

Best

D

[Trooz]

- I've created "Centre-Info" table to be as dim-table (In this table I deleted the duplicated values to have only the centres as a unique value" then linked this table to the fact table "Sales-Info"

So what should be the next step then? 🙂 

[harshnathani]

Hi @Trooz 

Number of Store Sold In =

CALCULATE(DISTINCTCOUNT('Sales-Info'[CentreId]),ALLEXCEPT('Sales-Info','Sales-Info'[ProductName],'Sales-Info'[CentreId]))

Number of Store Sold In =

CALCULATE(DISTINCTCOUNT('Sales-Info'[CentreId]),ALLEXCEPT('Sales-Info','Sales-Info'[ProductName],'Sales-Info'[CentreId]))

Regards,

Harsh Nathani

[Trooz]

@harshnathani  Thanks very much 🙂

[harshnathani · Accepted solution]

Hi @Trooz ,

Please use below measures:

Number of Store Sold In =

CALCULATE(DISTINCTCOUNT(Data12[CentreId]),ALLEXCEPT(Data12,Data12[ProductName],Data12[CentreId]))

Quantity Sold =

CALCULATE(SUMX(Data12,Data12[Quantity]),ALLEXCEPT(Data12,Data12[ProductName],Data12[CentreId]))

Regards,

Harsh Nathani

[Trooz]

Thank you, It worked 👍

Could you please explain why did chose this specific formulas "Countdistinct" and "Allexcept" ... I mean what is the moral of using those formulas?

[harshnathani]

Hi @Trooz ,

Think of ALL Except as as 'Grouping" in Business Terms.

For Quantity : I want to SUM the Quantity by the Product Name and the Centre Id.

For Store Sold : I want to Count the stores by Product Name and Centre Id And I do not want to count a store which comes in this group twice, thus I should use Distinct.

I would also suggest that you follow @Anonymous solution because you should get the data models correct which will help scale your solution and avoid any performance issues.

Fact and Dimension tables are key to get your solution working correctly.

Thanks and Regards,

Harsh Nathani

[Trooz]

Thanks a lot for your help. I've created a new dim table and in case of problems I'll get back to you 🙂