Level of Details Calculations : To show data fixed at different levels

[ncbshiva]

Hi Team,

I have a requirement to show that difference between two measures A and B at certain levels with all the below dimensions.

Dimensions : AEM,AEM Group,Product,Product Type,Brand,Sold-to and Ship-To.

Below is my DAX expression which i have written,

CALCULATE(IF([A]-[B])*[C]);

ALLEXCEPT('Ship-To Party (BV)';'Ship-To Party (BV)'[Ship-To Party (BV)]);ALLEXCEPT('Sold-To Party (BV)';'Sold-To Party (BV)'[Sold-To Party (BV)]))

In my above DAX expression , i am bale to include not more than 3 dimensions with ALLEXCEPT.

So can anyone help me to write the DAX expression to fix the calculations at all the dimension levels mentioned.

Regards

SHIVA

[v-juanli-msft]

Hi @ncbshiva 

Please share an example and expected result on the report so that i can test.

Here is my example

Measure 5 =
CALCULATE (
    [Measure_value1] - [Measure_value2],
    FILTER (
        ALL ( Table1 ),
        Table1[AEM] = MAX ( Table1[AEM] )
            && Table1[AEM Group] = MAX ( Table1[AEM Group] )
            && Table1[Product] = MAX ( Table1[Product] )
            && Table1[Product Type] = MAX ( Table1[Product Type] )
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @ncbshiva 

Assume

[Meausre A]=SUM(Table[sales])

[Meausre A]=CALCULATE(SUM(Table[sales]),SAMEPERIODLASTYEAR(Table[Date]))

then to compare [Measure A] and [Measure B]

You could add all dimensions in the "Row" field of the matrix,

add [Measure A] and [Measure B] in the 'Value" field,

then you can see different values each shows based on the dimensions.

You can also use the formula to show difference

[difference]=[Measure A]- [Measure B]

aslo add all dimensions in the "Row" field of the matrix.

If you have a specific situation, please share some data examples.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ncbshiva]

Hi @v-juanli-msft ,

I wanted to know how to calcuate some measures at different dimension levels.

For Example i want to calculate the difference between 2 measures at below dimension levels

AEM,AEM Group,Product,Product Type,Brand,Sold-to and Ship-To.

This means that difference should always be calculated at the above mentioned combination of dimensions always ,

regardless whether they exist in my visuals or not. The same can be done in Tableau by using FIXED Level of details expression. I want to know how can the same be done in Power BI.

Regards,

SHIVA

[ncbshiva]

@v-juanli-msft ,

Can you help me with this requirement ?

Regards

SHIVA