October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
Dear experts,
I have already received many good tips and advice here. So I hope you can help me totday again. I'm racking my brains to find out why the total sum per row for a measure doesn't show the correct value, as you can see in the tabel below for "Total (Current + Forecast)", while the separate measures are summed up correctly
Measure | A | B | C | D | E | Total |
Total (Current + Forecast) | 23 | 47 | 31 | 54 | 27 | 112 |
Current | 23 | 47 | 70 | |||
Forecast | 31 | 54 | 27 | 112 |
All three are measures, created as follows
Current = SUM('tblA'[Statusquo])
Forecast = if ((SUM('tblB'[Future]) - SUM('tblA'[Statusquo])) < 0, SUM('tblB'[Future]), SUM('tblB'[Future]) - SUM('tblA'[Statusquo])
Total (Current + Forecast) = Current + Forecast
I really would much appreciate to get any support to find my mistake 🤔. Thanks a lot in advance
Baerbel
Solved! Go to Solution.
Hi @Baerbel ,
Please refer to the pbix file to see if it helps you.
Create measures.
Future_adj_1 =
VAR _a = [Future_adj]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Future_adj] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Future_adj_sumx_1 =
VAR _a = [Future_adj_sumx]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Future_adj_sumx] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Statausquo + Future_adj_1 =
VAR _a = [Statausquo + Future_adj]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Statausquo + Future_adj] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the link from where i can download your PBI file.
Thanks @Ashish_Mathur for taking time - in the meantime @Anonymous was able to help me - the link to the file is part of polly's post.
All the best
Bärbel
Hi @Baerbel ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create a measure.
Measure =
VAR _a = tblB[TOTAL]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", tblB[TOTAL] )
RETURN
IF ( HASONEVALUE ( tblB[subject] ), _a, SUMX ( _b, [aaa] ) )
If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
thanks for taking time to help me and the PBIX File. If I understood correctly you re proposing to create a new table bringing together the different data - this I have to try, as my basic tables are a bit more complex. I tried to put it in your pbix-File a a new page - where I put in my current beginnings of creating measures with probably a lot of beginner-mistakes. If you have a little bit more time to invest in my learnings I would appreciate it. Thanks in advance.
Total for a measure don't sum up correctly in a row.pbix
Bärbel
Hi @Baerbel ,
Please refer to the pbix file to see if it helps you.
Create measures.
Future_adj_1 =
VAR _a = [Future_adj]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Future_adj] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Future_adj_sumx_1 =
VAR _a = [Future_adj_sumx]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Future_adj_sumx] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Statausquo + Future_adj_1 =
VAR _a = [Statausquo + Future_adj]
VAR _b =
SUMMARIZE ( tblB, tblB[subject], "aaa", [Statausquo + Future_adj] )
RETURN
IF ( HASONEVALUE ( tblC[subject] ), _a, SUMX ( _b, [aaa] ) )
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
it works 😀 happy - Thanks so much for your help 😍- I now have a solution and learnt a lot about creating the right measures.
All the best
Bärbel
@Baerbel use the correct column in summarize or values
sumx(values(Table[Column]), calculate( if ((SUM('tblB'[Future]) - SUM('tblA'[Statusquo])) < 0, SUM('tblB'[Future]), SUM('tblB'[Future]) - SUM('tblA'[Statusquo]) )))
or
sumx(summarize(Table, Table[Column],"_1", calculate( if ((SUM('tblB'[Future]) - SUM('tblA'[Statusquo])) < 0, SUM('tblB'[Future]), SUM('tblB'[Future]) - SUM('tblA'[Statusquo]) ))), [_1])
@amitchandak thanks for your quick reply and sorry for asking again, as I'm a beginner - sumx(values(Table[Column]) -- what does it refer to, as I'm "in a matrix visual", where I combined "Current" and "Futture" from two different tables - Does ist mean, that I've to create a new table to get the right total? For all other it works fine
Thanks for helping me
Baerbel
User | Count |
---|---|
103 | |
98 | |
96 | |
78 | |
41 |
User | Count |
---|---|
139 | |
137 | |
126 | |
99 | |
62 |