CODEWITHSUNDEEP
powerbidax
Majed elmohands
Majed elmohands

Reputation: 21

How to different calculation on different granularity?

I want to achieve the following:

example

The idea is to calculate the sum product in the following scenario:

How can I achieve that in DAX?!

Upvotes: 0

Views: 772

Answers (1)

Olly
Olly

Reputation: 7891

It depends a bit on how your data model looks...

With a model like this:

enter image description here

Your DAX measure may look something like this:

Weighted Amount = 
    SWITCH ( 
        TRUE(),
        ISINSCOPE ( Level2[Level 2] ), 
        SELECTEDVALUE ( Level2[Amount] ),
        ISINSCOPE ( Level1[Level 1] ),
        SUMX ( 
            Level2,
            Level2[Amount] * Level2[Weight 2]
        ),
        SUMX ( 
            Level1,
            CALCULATE ( 
                SUMX ( 
                    Level2,
                    Level2[Amount] * Level2[Weight 2]
                )
            ) * Level1[Weight 1]
        )
    )

We're using SWITCH and ISINSCOPE to determine the level at which the measure is being evaluated, then following your business logic for each level.

enter image description here

Upvotes: 1

Related Questions