Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I have data like this:
category = dim table
value last week = calculation table
value current week = calculation table
diff = calculation table
category | value last week | value current week | Diff | sum of diff |
a | 88% | 88% | 0.00% | ? |
b | 87% | 90% | 3.00% | ? |
c | 0% | 0% | 0.00% | ? |
a | 69% | 70% | 1.00% | ? |
I need the value SUM of total diff (0%+3%+0%+1%) = 4%, the result should be:
category | value last week | value current week | Diff | sum of diff |
a | 88% | 88% | 0.00% | 4% |
b | 87% | 90% | 3.00% | 4% |
c | 0% | 0% | 0.00% | 4% |
a | 69% | 70% | 1.00% | 4% |
I try to create a measure but the result is still incorrect
my measure =
I hope i'm clear enough.
Thank you guys
Solved! Go to Solution.
Hi @tian ,
You can try this measure:
sum of diff =
VAR tab =
SUMMARIZE (
'calculation table',
'calculation table'[value current week],
'calculation table'[value last week],
"A",
CALCULATE (
SUM ( 'calculation table'[value current week] ),
ALL ( 'calculation table' )
)
- CALCULATE (
SUM ( 'calculation table'[value last week] ),
ALL ( 'calculation table' )
)
)
RETURN
SUMX ( tab, [A] )
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tian ,
You can try this measure:
sum of diff =
VAR tab =
SUMMARIZE (
'calculation table',
'calculation table'[value current week],
'calculation table'[value last week],
"A",
CALCULATE (
SUM ( 'calculation table'[value current week] ),
ALL ( 'calculation table' )
)
- CALCULATE (
SUM ( 'calculation table'[value last week] ),
ALL ( 'calculation table' )
)
)
RETURN
SUMX ( tab, [A] )
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is the category field in the visual from the dimension table?
please post some sample data
Proud to be a Super User!
Paul on Linkedin.
what is your [Diff] measure?
If [Diff] is delivering the correct total, then:
all diff = CALCULATE([Diff], ALL(Dim table[category]))
if it isn't, first create the SUMX measure and then:
all diff= CALCULATE([Sumx measure], ALL(Dim table[category]))
Proud to be a Super User!
Paul on Linkedin.
I have try your suggestion,
first I create the SUMX measure and then:
all diff= CALCULATE([Sumx measure], ALL(Dim table[category]))
but the result is total sumx not total sum all percentage of diff. not 4%.
Try:
all diff =
CALCULATE(
SUMX(VALUES('dim table'[category]),[Diff]),
ALL('dim table'[category]))
Proud to be a Super User!
Paul on Linkedin.
Thank you for the response, the result still incorrect same as my measure before.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
66 | |
54 | |
47 | |
36 | |
34 |
User | Count |
---|---|
84 | |
70 | |
58 | |
45 | |
44 |