Dear all,
I have stuck in calculating the Profit / Loss between two subgroups. I am pulling data from a single column and creating subgroup and group in power BI and drag that groups to Matrix table. But, I need to do some calculations as mentioned below, Please show me a way to achieve this result and it would be much appreciated.
1. Sum of Net Revenue - Sum of Cost of Revenue = Gross Profit (on a separate Total)
2. Sum of Other expenses + Gross Profit = Profit Other Expenses
3. Sum of Other income + Profit Other Expenses = Profit Before Tax
4. Sum of Tax + Profit Before Tax = Profit (Loss)
Note: We are using the formula for subtracting --> Sum of Net Revenue - Sum of Cost of Revenue = Gross Profit
Gross Profit = IF (
ISFILTERED ( sortedGroup[Column1 (groups)] ),
SUMX ( Sheet1, Sheet1[Amount] ),
SUMX (
FILTER (Sheet1,Sheet1[Account Reporting (groups)]= "Net Revenue" ),
Sheet1[Amount]
)
- SUMX (
FILTER (
Sheet1,
Sheet1[Account Reporting] = "Cost of Revenue"
),
Sheet1[Amount]
)
)
Many Thanks,
Praveen
Hi Praveen,
1. Add an index.
Index =
SWITCH (
[Account Balance sheet (groups)],
"Group A", 1,
"Group B", 2,
"Group C", 3,
"Group D", 4,
9999
)
2. Create a measure.
Measure =
IF (
ISFILTERED ( 'Single Column Data'[Account Balance sheet (groups)] ),
SUM ( 'Single Column Data'[Amount] ),
CALCULATE (
SUMX (
'Single Column Data',
IF (
[Index] IN { 2, 3 },
-1 * 'Single Column Data'[Amount],
'Single Column Data'[Amount]
)
),
FILTER (
ALL ( 'Single Column Data' ),
'Single Column Data'[Index] <= MAX ( 'Single Column Data'[Index] )
)
)
)
Best Regards,
Dale
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
