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.
I have a huge table, and example of four columns from the table is as follows:
In this table i have c_id (clients), p_id (periods) and val=values. I want to be able to calculate tot_val. The way I want tot_val to be calculated is, for example, for c_id=1 and p_id=19931201, we have that val = (10,15,30), so tot_val should return 55 on all three rows with c_id=1 and p_id=19931201. I tried many options but did not get the correct results. Please let me know if you have any suggestions on how to do this.
Solved! Go to Solution.
Calculate (sum(Val), allexcept(Cid,oid))
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Something like this?
TotalValue =
VAR Client = Bamba[C_ID]
VAR Periods = Bamba[P_ID]
VAR Outstanding = Bamba[Outstanding]
VAR SameRows =
FILTER (
Bamba,
Bamba[C_ID] = Client
&& Bamba[P_ID] = Periods
&& Bamba[Outstanding] <> 0
)
VAR Result =
CALCULATE ( SUM ( Bamba[Val] ), SameRows )
RETURN
IF ( Outstanding = 0, 0, Result )
HI @bamba98 ,
Create a measure
Total Value =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[C_Id]
= MAX ( 'Table'[C_Id] )
&& 'Table'[P_Id]
= MAX ( 'Table'[P_Id] )
&& 'Table'[Outstanding] > 0
)
)
Do a Visual Filter for Outstanding.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Calculate (sum(Val), allexcept(Cid,oid))
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you very much @stevedep , that works fine!
However, I noticed that there is another column "Outstanding" in my table that contains values which are zero. How can I exclude these rows in my calculation?
@AntrikshSharma This return the values of val in tot_val. Allow me to explain it better:
Let zoom in to c_id=1 and p_id=19931201.
What I want is for the expression to return the sum of val where outstanding is greater than 0. With the solution of @stevedep , I get tot_val = 110, but I want it to return 55 as it should ignore the rows where outstanding =0.
Something like this?
TotalValue =
VAR Client = Bamba[C_ID]
VAR Periods = Bamba[P_ID]
VAR Outstanding = Bamba[Outstanding]
VAR SameRows =
FILTER (
Bamba,
Bamba[C_ID] = Client
&& Bamba[P_ID] = Periods
&& Bamba[Outstanding] <> 0
)
VAR Result =
CALCULATE ( SUM ( Bamba[Val] ), SameRows )
RETURN
IF ( Outstanding = 0, 0, Result )
You can do this in many ways, with SUMX, ALLEXCEPT etc. Here's a pedestrian method:
tot_val =
var c= selectedvalue(Table[c_id])
var p= selectedvalue(Table[p_id])
return calculate(sum(Table[val]),allselected(Table),Table[c_id]=c,Table[p_id]=p)
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 |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |