DAX formula to calculate values from one specific row to the other

[pranays2]

Hi,

I'm new to power bi especially DAX.  I've a situation where i've 1main account name, sub account name and one amoun column now i want to divide one of the value from sub account name to another total of the account name group. It's easy to do in excel pivot can i get some help on how to perform the same thing in Power Bi using DAX

[danextian]

Hi @pranays2 ,

Given the available information, here's what I've come up with:

=
DIVIDE (
    [Sum of Amount],
    CALCULATE (
        [Sum of Amount],
        FILTER (
            ALL (
                'Table'[Column name of the first level in the row hierarchy],
                'Table'[Column name of the second level in the row hierarchy]
            ),
            'Table'[Column name of the first level in the row hierarchy] = "Deposits from bank"
        )
    )
)

 If this doesn't work, please provide a sample data.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071... 

[pranays2]

Hi danextian, Thanks for the quick response unfortunately the formula you suggested didn't work i've uploaded the sample data in pbix file please find it in the link attached https://drive.google.com/file/d/1GTchy22DAs332bsTsjy8fnU1KkoGsIgR/view?usp=sharing 

[danextian · Accepted solution]

Hi @pranays2 ,

Please try this:

% of deposits from banks = 
DIVIDE (
    SUM ( Sheet1[Amount] ),
    CALCULATE (
        SUM ( Sheet1[Amount] ),
        FILTER (
            ALL ( Sheet1[Account Name], Sheet1[Account name Sub 1] ),
            Sheet1[Account Name] = "Deposits from banks"
        )
    )
)

deposits from banks only

Deposits from banks =
CALCULATE (
    SUM ( Sheet1[Amount] ),
    FILTER (
        ALL ( Sheet1[Account Name], Sheet1[Account name Sub 1] ),
        Sheet1[Account Name] = "Deposits from banks"
    )
)

[pranays2]

Thanks very much danextian it's working now 🙂