[HELP] Weird problem while trying to SUM a CLUSTER (.pbix attached)

[tiago]

Hi guys, I am not a powerbi or DAX expert but I think what I am trying to do should work. I’ve created a fake dataset so I could share the data with you guys and mimic the problem.

Please check the .PBIX file attacherd.

All my tables are correctly related. I have one Table that is called PRODUCT AGE and  has This fields:

PRODUCT

COLOR

STORE

AGE

DAYS FROM LAST ITEM SOLD

Cluster Days Last Item Sold

I have one Table called COLOR (that has all unique colors and is related with the product age table)

I have one Table called PRODUCTS that has all unique products and is related with PRODUCT on the product age table.

I have one table called Branch that has unique store IDS and the STORE name

Lastly I have another table that is STOCK that has the columns PRODUCT, COLOR, STORE, STOCK – PRODUCT is related to PRODUCT column on the PRODUCTS table and COLOR is related to the COLOR column on the COLORS table and STORE in related to Store Name on BRANCH table.

My problem is that I wanted to make a cluster so I could know what percentage of my items were sold between each of this clusters

1 - between 0 to 5 days

2 - between 6 to 10 days

3 - between 11 to 30 days

4 - between 31 to 90 days

5 - more than 90 days

When I try to sum stock on the clusters only the SUM does not work. What am I doing wrong?

The relations are working If I try to make a more detailed report so I could see product, color, store, stock, days from last item sold, and cluster it works flawlessly on the micro level but it doesn’t work when I remove all columns and leave only cluster and stock.

This should be the expected result for this data set, but it isn’t working.

1 - between 0 to 5 days	691
2 - between 6 to 10 days	321
3 - between 11 to 30 days	248
4 - between 31 to 90 days	230
5 - more than 90 days	202

If you want to download the PBIX file I've atteched it on Dropbox

Download PBIX File

Any Help is appreciated.

Thanks

[Isaias_Garcez · Accepted solution]

Click here and see its name, right-click on a cause.

Note that the PRODUCT AGE table does not filter the STOCK table, so Cluster Days's Last Sold item categories are not counted individually in Inventory.

Now when you put the product into Play, it works because the PRODUCTS table has filters for the two tables in question [STOCK and PRODUCCT AGE].

Possible solution: Create the last item sold item from cluster days in the product table, as this ends up as a product resource, so this product feature will filter the material. another thing would be a double filter relationship between PRODUCT AGE and PRODUCTS, so this first table can filter STOCK.

However, the results are not consistent as I realized that you have the same product in different stores with different inventory time. But then, with a glorious CALCULATE, FLY THERE.