计算列作为轴 &价值 [英] Calculated columns as axis & value

问题描述

我有一堆计算出来的 DAX 列，我想将它们显示为视觉对象.如果我使用普通条形图，我会得到下图，条形图 1，因为我在轴字段中没有任何字段.每个计算列的标题是我希望 x 轴类似于下面漏斗图中的样子.

漏斗图只需要填写值字段，它会创建以下图像，这是我想要的，但它需要与上一张图像类似垂直.

最后一张图，条形图 3 是我想用我的计算列实现的，但到目前为止我还没有弄明白这一点.此视觉效果是使用与我现在正在处理的项目无关的不同文件创建的.我相信，如果我可以取消透视计算的列，那么它将创建我正在寻找的图表，但我无法弄清楚如何取消透视在 DAX 中创建的列.有没有办法取消 DAX 列或市场上的视觉效果来完成我想要做的事情?还是我需要创建自己的自定义视觉来完成此操作?其他想法/想法?

并允许您创建一个条形图，其轴为 Month，值的总和为 MonthCost.

I have a bunch of calculated DAX columns that I want to show as a visual. If I use a normal bar chart I get the following image, Barchart 1, where because I do not have any fields in the axis field. The titles of each of the calculated columns are what I want the x-axis to be similar to how it is in the funnel chart below.

The funnel chart only requires the value field to be filled in and it creates the following image which is kind of what I want but it needs to be vertical similar to the last image.

This final image, Barchart 3 is what I want to achieve with my calculated columns but so far I have had no luck in figuring this out. This visual was created using a different file which is irrelevant to the project I am working on now. I believe that if I could unpivot the calculated columns then it would create the graph I am looking for but I can't figure out how to unpivot columns that are created in DAX. Is there a way to unpivot DAX columns or a visual on the marketplace to accomplish what I am trying to do? Or would I need to create my own custom visual to accomplish this? Other ideas/thoughts?

Sample data file

解决方案

I'd recommend creating a calculated table that has Month unpivoted so that you only need to put a single series on the bar chart.

For example, you can write a calculated table like this with only 7 columns:

CalcTable = 
VAR ThisYear = YEAR ( MAX ( Sheet4[Start] ) )
RETURN
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS (
                Sheet4,
                "Project", Sheet4[Project],
                "Start", Sheet4[Start],
                "End", Sheet4[End],
                "Cost", Sheet4[Cost]
            ),
            ADDCOLUMNS (
                GENERATESERIES ( 1, 12 ),
                "Month", FORMAT ( DATE ( ThisYear, [Value], 1 ), "MMMM YYYY" )
            )
        ),
        "MonthCost", IF (
            [Value] >= MONTH ( [Start] ) && [Value] <= MONTH ( [End] ),
            DIVIDE ( [Cost], DATEDIFF ( [Start], [End], MONTH ) + 1 ),
            0
        )
    )

This table looks like this:

And allows you to create a bar chart with Month on the axis and sum of MonthCost for the values.

这篇关于计算列作为轴 &价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！