Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community,
Under 100% stacked column chart, can I have the chart considering negative value and show the % (yellow cells) as below?
It looks like the chart will convert negative numbers to positive as the base of the total amount.
Thanks all.
Solved! Go to Solution.
Hi @nctvhk ,
Sorry for my late reply.
Here's my solution.
1.Unpivot the table in Power Query
2.Because there can only be one field in the axis, we have to merge the two columns to create the following calculated column.
P&U = [Period]&"-"&[Unit]
3.Create the following measure
Measure = DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[P&U]),[Attribute]="Sales")))
4.The result circled in the red box is the result you want, you can check it.
More details in the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nctvhk ,
Sorry for my late reply.
Here's my solution.
1.Unpivot the table in Power Query
2.Because there can only be one field in the axis, we have to merge the two columns to create the following calculated column.
P&U = [Period]&"-"&[Unit]
3.Create the following measure
Measure = DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[P&U]),[Attribute]="Sales")))
4.The result circled in the red box is the result you want, you can check it.
More details in the attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nctvhk
You can use stacked column chart and put your category column which consist value like EBITA etc under legend and put your percentage column under Values
It will show your negative value as it is.
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thanks @Samarth_18 ,
My dataset is like this:
In this case if I use the stack column solution you provided and take the hard coded percentage column as value, when I select filter/slicer (e.g. Period 1, Unit A+B), it seems the result will just average/sum the hard-coded percentage instead of showing the correct %.
Thanks.
you need to unpivot the table and create a measure
Measure = sum('Table'[Value])/CALCULATE(sum('Table'[Value]),ALLEXCEPT('Table','Table'[Period]))
and use stacked column
pls see the attachment below
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |