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 have a dataset that looks like this.
Item | Period | DCP | RESERVES | RATES | LOANS | CONTRIBUTIONS | GRANTS | OTHER |
1001 | 1 | 3800000 | ||||||
1002 | 4 | 19264000 | 3799495 | |||||
1005 | 12 | 2540000 | 0 | |||||
1008 | 8 | 0 | 1185057 | |||||
1021 | 1 | 4634105 | 1500000 | 1500000 | ||||
1021 | 3 | 5485408 | 1034999 | |||||
1025 | 5 | 3085987 | 265190 | 2020000 |
I'm trying to calculate the total of (DCP + RESERVES + RATES + LOANS + CONTRIBUTIONS + GRANTS + OTHER) for each Period
only for the items (not rows) that the sum of DCP is greater than 0.
Any thoughts?
Thanks in advance.
Hi @Anonymous
try a measure
Measure = CALCULATE(SUMX('Table',[DCP]+[RESERVES]+[RATES]+[LOANS]+[GRANTS]+[CONTRIBUTIONS]+[OTHER]), ALLEXCEPT('Table','Table'[Period]),'Table'[DCP]>0)
Hi @az38 ,
I tried that and it only gives the sum of rows where DCP is greater than 0. What I'm trying to achieve is to get the total of items (for each period) if an item has a value in the DCP column for any period.
Hope this makes sense.
Thanks
Hi @Anonymous
not sure I understand you well but try
Measure =
var _isDCP = CALCULATE(COUNTROWS(Table), ALLEXCEPT('Table','Table'[Period]),'Table'[DCP]>0)
RETURN
if(_isDCP > 1,
CALCULATE(SUMX('Table',[DCP]+[RESERVES]+[RATES]+[LOANS]+[GRANTS]+[CONTRIBUTIONS]+[OTHER]), ALLEXCEPT('Table','Table'[Period])),
BLANK()
)
if it will not work as you need, please show a desired output based on your dummy data
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |