To Summarize table to display into stacked chart

[Anonymous]

Hi ,

I have been struggling ....I have my data with sum of total for all the stages : 

1) Current Spend

2) Project

3) Budget

and I hope to stack by region (2nd image) and , i could do it in excel... but in powerBi, I have been struggling as i am still not used to DAX language... I read on Summarized, but keep getting errors on the DAX codes... thus, hope to get advise from the forum.

Looking forward to your kind advise, Thanks, Val 

[Anonymous · Accepted solution]

Hi @Anonymous ,

If your table data looks like this.I don't think there is any need to add Qtr.

All you need to do is add the field to the visual and it will filter the data accordingly.

Best Regards,
Wenbin Zhou

[Anonymous]

Hi @Anonymous ,

If I understand it wrong, please provide simple data.

Regarding your question, if your data is similar to what I assume, I don't think you need to use the 'Summarize' function, you can just use the 'Sum' function directly.

Please follow these steps:

1.Manually enter the following data.

2.Put these two fields into the visual as the x-axis(It is normal to display an error because there is no relationship between the two tables)

3.Use the 'Selectedvalue' function to determine which stage to calculate.

SELECTEDVALUE function - DAX | Microsoft Learn

Measure = 
var _whichstages = SELECTEDVALUE('Table 2'[Total])
RETURN 
SWITCH(TRUE(),
_whichstages = "Current Spend Amt",SUM('Table'[Current Spend]),
_whichstages = "total Projected",SUM('Table'[Projected])
_whichstages = "total Budget Amt",SUM('Table'[Budget]) 
)

4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear WenBin,

Just for the nice idea, using the selectedValue, I manage to come close to the result, however, my result X-axis currently display ...Does that means I need to add Qtr into my dummy table - Table 2?

Q1 Current Spend Amt

Q1 tota Projected

Q1 Total Budget Amt

instead of having the Q1 Grouped.... maybe due to my dataset not in Row format.... Thank you so much again for your kind advise.

Quarter	Region	Current Spend	Projected	Budget
Q1	APAC	9000	10000	12000
Q1	EUROPE	29000	31000	35000
Q1	US	26000	29000	27000
Q1	LATIN	4000	4000	4000
Q2	APAC	13000	13000	15000
Q2	EUROPE	35000	38000	37000
Q2	US	22000	25000	22000
Q2	LATIN	4000	5000	4000
Q3	APAC	13000	19000	14000
Q3	EUROPE	33000	42000	36000
Q3	US	28000	39000	26000
Q3	LATIN	7000	13000	7000
Q4	APAC	13000	30000	17000
Q4	EUROPE	35000	54000	40000
Q4	US	29000	57000	32000
Q4	LATIN	7000	21000	7000

[Anonymous · Accepted solution]

Hi @Anonymous ,

If your table data looks like this.I don't think there is any need to add Qtr.

All you need to do is add the field to the visual and it will filter the data accordingly.

Best Regards,
Wenbin Zhou

[Anonymous · Accepted solution]

Dear @Wenbin Zhou,

it's works for one of my data models 🙂

I shall look into my other to see where could go wrong then. Many thanks for your kind help, the extremely helpful tips, great enlightenment for me.

Cheers, Val