Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
I am working n a report, need show "Diff" for two date,
in the "Diff" measure, some is greater than 0, it is the increase
some is less than 0, it is the decrease,
I need to calculatethe total "Diff” where all Industries greater than 0, (all the increased amount for Industry)
for example , I need get 128 for total increase.
Here is related file link :
https://www.dropbox.com/s/h2yj6w6ylvjdtcd/Diff.pbix?dl=0
Thank you ahead for any idea for this measure
Solved! Go to Solution.
You can use and IF condition so it returns blank for those with negative [Diff]
Total Increase ALL =
IF (
[Diff] > 0,
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
)
Proud to be a Super User!
Hi @wangjuan303 ,
Please try this:
Total Increase =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
'Fact Table',
'Fact Table'[Industry],
'Fact Table'[Scenario],
"Diff", [Diff]
),
"x", IF ( [Diff] > 0, [Diff] )
),
[x]
)
Alternative formula
Total Increase =
CALCULATE (
[Diff],
FILTER (
SUMMARIZE ( 'Fact Table', 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
Proud to be a Super User!
@danextian Thank you a lot for your suggestion, I test it, I need in "Total Increase" column always show total value "128" (total of all Industry where they are Diff >0) , then use this value to calculate for "Increase%", Do you have any idea, Thank you again
So what you want is to have 128 in all rows? Try this:
Total Increase ALL =
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
Proud to be a Super User!
Thank you so much, How can we just show Industry where Diff>0, for example, in this report, "Total Increase ALL" should like Total Increase show blank,
You can use and IF condition so it returns blank for those with negative [Diff]
Total Increase ALL =
IF (
[Diff] > 0,
CALCULATE (
[Diff],
FILTER (
ALL ( 'Fact Table'[Industry], 'Fact Table'[Scenario] ),
VAR _diff = [Diff] RETURN _diff > 0
)
)
)
Proud to be a Super User!
Thank you for your help
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 |
---|---|
109 | |
102 | |
85 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |