show all values for dimension

[vireshkolagimat]

Hi All,

I need to show the total no of items available in each BU from the dimension table. 

I used the expression as count(distinct ItemNumber) and it show as 85. By the movement i expand the pivot table, the numbers are splitting. Even after expansion, i need to show the same number i.e. 85 for both customer group and name level.

How to achieve the same. Thanks.

Below screenshot for your reference.

[jonathandienst]

Count(distinct ...) will produce different totals if you dimension the expression differently. The partial total expression on BU would return the same value as the first for the partial, but the problem is that there ARE 53 distinct SKUs in the first customer group and 63 distinct SKUs in the second customer group.

Its easy to adjust the first table to produce the more granular result in he first chart(sum of 126) with an expression like 

Sum(Aggr(Count(Distinct SKU), BU, Customer_Group))

 I am not sure if the reverse is possible, as the count (and the distinct) will always be split by the dimensions. You may have to look at doing something in the load script or rethinking the sheet objects. In any case, reporting values other than 53 and 63 would be untrue.

[vireshkolagimat]

Hi, The expected output should be like below. The total sku's should be 85 at BU and Customer group level and it should be same if i add any other dimension. The idea is to calculate out of the total sku's, how many are available at the customer location.

I used your expression but its not giving the expected result.

Thank you.

BU	Customer_Group	# SKU's	Customer count	Total SKU	Availability	Price Tag	Availability %	Price Tag %
CA1	XX005	85	1	85	53	52	62%	61%
CA1	XX008	85	1	85	63	55	74%	65%