Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
)
)
Proud to be a 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!
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
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"
)
)
Proud to be a Super User!
Thanks very much danextian it's working now 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |