Calculated column to categorize depending on a measure is behaving strangely

[samlopez313]

Hi, 

I'm not sure if this is possible or if I'm doing it correctly but here is my problem (had to red-tape some fields): 

I have a table with multiple accounts that have different SKUs available to them. In this table I have a measure that does a DISTINCTCOUNT over the SKUs to know how many different SKUs an account might have. 

I created a new table with only the DISTINCT accounts and added a calculated column that uses the measure discussed above to create a categorization of how many SKUs the account has. 

It seemed to be working but under closer inspection I noticed that sometimes an account would fall under a category like "More than 10 SKUs" but in the DISTINCTCOUNT of the SKUs it would only have 4. 

Why would this be happening? 

Here are some examples of the data: 

As you can see this account ID only has 4 different SkuIds but is showing under the "More than 10" Category.

This is my table structure: 

This is the main table with the details: 

This is the table I created with the DISTINCT Tenant values and the calculated column for the category: 

This is my measure for the distinct count:

DCSkuId = DISTINCTCOUNT('More than 1 SKU'[SkuId])

And this is the calculated column logic: 

SKUId DC Category = IF([DCSkuId]=1,"One Id Only",IF([DCSkuId]=2,"2 Id Only", IF(AND([DCSkuId]>=3,[DCSkuId]<=5),"3-5",IF(AND([DCSkuId]>=6,[DCSkuId]<=10),"6-10",IF([DCSkuId]>=11,"More than 10","Uknown")))))

Why are some accounts beign mis-categorized when I see the correct count in the sku distinct count? 

Here is another example: 

If you look at the first account (that ends on 96f0), you can see that in the SKUId it's only one distinct value even though it has 2 different subscription id's with the same SKU id (making the distinct count 1)  but it's still categorized as "2 Id Only", if I remove the subscription id and SKU Id columns to summarize it looks like this: 

As you can see in the summarized view it still says 1 for the account that ends on "96f0" for the DISTINCTCOUNT SkuId which is correct but the calculated column category is incorrect, what is happening in the background that makes this incorrect? 

Thanks,

Let me know if any other data is needed. 

Samuel Lopez.

[lbendlin]

You cannot create a calculated column or calculated table from a measure.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523