The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Dear Power BI Gurus,
Please help to build below DAX, I need to make sum based on condition on different Tables and Sum them together
I tried to use below DAX but not working.
TOTAL IFC ALL REVENUE ACTUAL = CALCULATE ( SUM(IFC_Monthly[Value]),IFC_Monthly[Category]="Revenue", IFC_Monthly[Period]="Actual"
+ SUM(CPU_Monthly[Value]),CPU_Monthly[Category]="Revenue", CPU_Monthly[Period]="Actual"
+ SUM(LOUNGE_Monthly[Value]),LOUNGE_Monthly[Category]="Revenue", LOUNGE_Monthly[Period]="Actual"
+ SUM(AIRLINES_Monthly[Value]),AIRLINES_Monthly[Category]="Revenue", AIRLINES_Monthly[Period]="Actual"
+ SUM(MD_Monthly[Value]),MD_Monthly[Category]="Revenue", MD_Monthly[Period]="Actual"
)
Solved! Go to Solution.
@jkhan
Can you use this measure which I modified.
TOTAL IFC ALL REVENUE ACTUAL =
VAR ifc =
CALCULATE (
SUM ( IFC_Monthly[Value] ),
IFC_Monthly[Category] = "Revenue",
IFC_Monthly[Period] = "Actual"
)
VAR cpu =
CALCULATE (
SUM ( CPU_Monthly[Value] ),
CPU_Monthly[Category] = "Revenue",
CPU_Monthly[Period] = "Actual"
)
VAR lounge =
CALCULATE (
SUM ( LOUNGE_Monthly[Value] ),
LOUNGE_Monthly[Category] = "Revenue",
LOUNGE_Monthly[Period] = "Actual"
)
VAR md =
CALCULATE (
SUM ( MD_Monthly[Value] ),
MD_Monthly[Category] = "Revenue",
MD_Monthly[Period] = "Actual"
)
RETURN
ifc + cpu + lounge + md
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
I need aggregiate Sum from Different Tables like IFC_Monthly CPU_Monthly LOUNGE_Monthly based on condidtion
IFC_Monthly[Category]="Profit", IFC_Monthly[Period]="Actual"
@jkhan
Can you use this measure which I modified.
TOTAL IFC ALL REVENUE ACTUAL =
VAR ifc =
CALCULATE (
SUM ( IFC_Monthly[Value] ),
IFC_Monthly[Category] = "Revenue",
IFC_Monthly[Period] = "Actual"
)
VAR cpu =
CALCULATE (
SUM ( CPU_Monthly[Value] ),
CPU_Monthly[Category] = "Revenue",
CPU_Monthly[Period] = "Actual"
)
VAR lounge =
CALCULATE (
SUM ( LOUNGE_Monthly[Value] ),
LOUNGE_Monthly[Category] = "Revenue",
LOUNGE_Monthly[Period] = "Actual"
)
VAR md =
CALCULATE (
SUM ( MD_Monthly[Value] ),
MD_Monthly[Category] = "Revenue",
MD_Monthly[Period] = "Actual"
)
RETURN
ifc + cpu + lounge + md
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
I used like below, Thanks for your reply.
TOTAL IFC ALL PROFIT ACTUAL =
CALCULATE ( SUM(IFC_Monthly[Value]),IFC_Monthly[Category]="Profit", IFC_Monthly[Period]="Actual" )
+
CALCULATE (SUM(CPU_Monthly[Value]),CPU_Monthly[Category]="Profit", CPU_Monthly[Period]="Actual" )
+
CALCULATE (SUM(LOUNGE_Monthly[Value]),LOUNGE_Monthly[Category]="Profit", LOUNGE_Monthly[Period]="Actual" )
+
CALCULATE (SUM(AIRLINES_Monthly[Value]),AIRLINES_Monthly[Category]="Profit", AIRLINES_Monthly[Period]="Actual" )
+
CALCULATE (SUM(MD_Monthly[Value]),MD_Monthly[Category]="Profit", MD_Monthly[Period]="Actual" )
@jkhan
You cannot add anything to the filter within CALCULATE like you have done + SUM ( CPU_Monthly[Value] ) .
Can you explain what is the logic that you need to apply to do the calculation also show picture of your model.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |