Calculation at a fixed level of detail

[Anonymous]

Hi all,

I'm struggling with what I'd call in a tableau world a fixed level of detail expression. In DAX I'm aware they work differently, I've successfully used CALCULATE and ALLEXCEPT in previous formulas where the filter is binary. This one I'm stuck with!

In the below example I am filtered to a store. Sales density is simply Weekly Average Sales/ Traited. I'm wanting the final column 'Sales Density Test' to be the same calculation but fixed to a banding in the dataset based on the store size and its banding. Therefore when you look at Baby you can see the sale density for that store, but also the average sales density in comparable stores.

The size banding could be one of eight options rather than a fixed option. That option changes depending on what store you filter to.

My (failed) attempt below:

Sales Density Test = CALCULATE(SUM('Datacube Sales Info CSV'[Weekly Average Sales])/SUM('Datacube Sales Info CSV'[Traited]),ALLEXCEPT('Size Banding Data','Size Banding Data'[Store]))

Any help or ideas are appreciated. I've combed through several posts but none seemed applicable

[Anonymous · Accepted solution]

So I believe I have fixed this myself. Solution for anybody who stumbles across the post.

The formula was falling down as I was misunderstanding ALLEXCEPT. The way to understand it my side is it creates a blank slate, you then let factors through one by one.

In the below formula I could simply copy the original Weekly Average Sales/ Traited calculation. With no filter criteria applied the calculation mimiked the original. One by one I allowed criteria to influence the calculation, eventually ending up with my answer below.

Sales Density Test = CALCULATE([Sales Density], ALLEXCEPT('Datacube Sales Info CSV','Datacube Sales Info CSV'[Size Banding],'Datacube Sales Info CSV'[POG_Cat_Desc],'Datacube Sales Info CSV'[Area],'Datacube Sales Info CSV'[Department],'Datacube Sales Info CSV'[Division]))