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

问题描述

我有一堆要显示为视觉效果的DAX计算列。如果使用普通的条形图，则会得到下图Barchart 1，因为在axis字段中没有任何字段。我希望每个计算列的标题都是x轴，类似于下面的漏斗图中的x轴。

漏斗图只需要填写value字段，它会创建以下图像，这是我想要的那种图像，但它必须垂直于上一张图像。

最后一张图像，即Barchart 3，是我想通过计算所得的列实现的，但到目前为止，我还没有运气来解决这个问题。该视觉效果是使用其他文件创建的，该文件与我现在正在处理的项目无关。我相信，如果我可以取消对计算列的透视，那么它将创建我要查找的图形，但是我无法弄清楚如何取消在DAX中创建的列。有没有办法解开DAX列或市场上的视觉效果来完成我想做的事情？还是我需要创建自己的自定义视觉效果来实现此目的？其他想法/想法？

并允许您使用月和值的 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屋！