Forum Discussion
To Summarize table to display into stacked chart
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
4 Replies
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.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