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 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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |