Total for a measure don't sum up correctly in a row

[Baerbel]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Baerbel]

Thanks @Ashish_Mathur for taking time - in the meantime @v-rongtiep-msft was able to help me - the link to the file is part of polly's post.

All the best

Bärbel

[v-rongtiep-msft]

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.

[Baerbel]

Hi @v-rongtiep-msft 

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

[v-rongtiep-msft · Accepted 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.

[Baerbel]

@v-rongtiep-msft 

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

[amitchandak]

@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])

[Baerbel]

@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