Search code examples
powerbidaxpowerquerycalculated-columnsm

Calculated columns as axis & value

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.

Barchart 1

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.

Barchart 2

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?

Barchart 3

Sample data file

Solution

  • 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:

    Calculated Table

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

    Bar Chart