英文:
How to create PowerBI waterfall charts showing a target profit, amounts above and below budget, and the actual profit?
问题
我想在PowerBI中创建一个瀑布图，从预算中预期的利润值开始，然后显示与预算的差异，然后显示实际利润。这是在Excel中的示例，将目标和实际值"设置为总计"。
在Excel中很容易做到这一点，但根据我的搜索，PowerBI的普通瀑布图选项中似乎没有"设置总计"选项？
到目前为止，我找到的最佳选项是下载Ultimate Waterfall并使用它。但Ultimate Waterfall会显示水印/收费，并且我似乎无法将原始总计的名称设置为我想要的名称 - 它只显示为"起始值"，而不是月度利润的目标值。
在PowerBI内是否有更好的选项？谢谢！
英文:
I would like to create a waterfall chart in PowerBI that starts with the value expected for profit in the budget, then shows variances against budget in both directions, then shows the actual profit made. This is what it looks like in Excel, with the target and actuals "Set as Total".
This is easy to do in Excel but from Googling there is apparently no "Set Total" option in PowerBI's normal Waterfall chart option?
This is the best option I have found so far - download Ultimate Waterfall and use that. But Ultimate Waterfall shows a watermark/charges/and I can't seem to set the original Total to have the name I want - it just shows as "Starting Value" rather than as the target profit for the month.
Are there better options within PowerBI? Thanks!
答案1
得分: 1
以下是翻译好的部分：
两种可视化方式：
1. 在PowerQuery中添加[Index]列以进行排序：
2. 按索引设置项目列排序：
3. [选项1：使用标准瀑布图可视化]
"目标利润"柱不能更改颜色，"实际利润"柱不能更改名称
- 将瀑布排序轴设置为"按项目升序排序"：
- 过滤页面以删除"实际利润"，并在可视化设置页面打开"显示总计列"。
3. [选项2：使用堆叠柱状图可视化]
所有柱的名称都是正确的，但颜色无法更改
两列计算公式：
Value assist = SWITCH(TRUE(),
[Index]=1,0,
[Index]=2,
IF([Value]>0,
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall')),
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+[Value]),
...
[Index]=6,0)
Value 2 = ABS([Value])
英文:
Two ways for your visual:
1. Add [Index] column in PowerQuery for order:
2. Set Item column sort by Index:
3. [option1: use standard waterfall visual]
"Target profit" bar cannot change color and "Actual profit" bar cannot change name
- Set waterfall sort axis as 'sort ascending by item':
- Filter page remove "Actual profit" and visual setting page open "Show total column"
3. [option2: use Stacked Column chart visual]
all bars name are correct but colors cannot change
two columns calculation formula:
Value assist = SWITCH(TRUE(),
[Index]=1,0,
[Index]=2,
IF([Value]>0,
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall')),
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+[Value]),
[Index]=3,
IF([Value]>0,
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall')),
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall'))+[Value]),
[Index]=4,
IF([Value]>0,
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=3,REMOVEFILTERS('Waterfall')),
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=3,REMOVEFILTERS('Waterfall'))+[Value]),
[Index]=5,
IF([Value]>0,
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=3,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=4,REMOVEFILTERS('Waterfall')),
CALCULATE(SUM([Value]),'Waterfall'[Index]=1,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=2,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=3,REMOVEFILTERS('Waterfall'))+CALCULATE(SUM([Value]),'Waterfall'[Index]=4,REMOVEFILTERS('Waterfall'))+[Value]),
[Index]=6,0)
*
Value 2 = ABS([Value])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论