cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

Hi @pranays2 ,

``````% 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"
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
4 REPLIES 4
Super User

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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Frequent Visitor

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

Super User

Hi @pranays2 ,

``````% 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"
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Frequent Visitor

Thanks very much danextian it's working now 🙂

Announcements