Forum Discussion

Dee's avatar
Dee
Icon for Helper III rankHelper III
6 years ago
Solved

show months data seperately on a graph

Hello world,

 

I am trying to achieve a combo chart that shows 

 

1. amount paid on time.

2.planned amount

3.late amount from previous months paid this month.

 

Ideally, I wanted a combo chart that would show the planned amount independently, and the on time and late stacked together, that's not possible in power bi unless I use the line and stacked chart.

 

That not being possible, is there a way I can maybe show for example.

May - Planned 100

May - On time 60

May - Late make up 50

 

A chart showing the three may value, and maybe I can try to reduce the spacing between them to be seen as independent and stacked. 

 

I am not sure If explained well and if there's a way to achieve this without using the line and combo chart. Any help would be highly appreciated.

 

I have uploaded the picture on top since powerbi only allows one month, can I have two months, and have one as an independent and the other one stacked.

 

Sorry for the poor pic explanation.

 

 link to pbix file 

 

TIA!

 

Need Help
Tips and Tricks
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Dee ,

     

    We can also use the axis table as a workaround:

     

    1. create a calculated table and sort by rank column

    Axis = 
UNION (
    CROSSJOIN (
        ADDCOLUMNS (
            SELECTCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "MonthNr", [Value] ),
            "Month", FORMAT ( DATE ( 2000, [MonthNr], 1 ), "MMM" ),
            "Rank", [MonthNr] * 2
        ),
        SELECTCOLUMNS ( { "On Time", "Late" }, "Legand", [Value] )
    ),
    CROSSJOIN (
        ADDCOLUMNS (
            SELECTCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "MonthNr", [Value] ),
            "Month", FORMAT ( DATE ( 2000, [MonthNr], 1 ), "MMM" ) & "-Planned",
            "Rank", [MonthNr] * 2 - 1
        ),
        SELECTCOLUMNS ( { "Planned" }, "Legand", [Value] )
    )
)

     

     

    2. create a measure to show the value

     

    Value = 
Var t = FILTER (
                ALLSELECTED ( 'export (2)' ),
                'export (2)'[Month]
                    IN SELECTCOLUMNS ( 'Axis', "Month", SUBSTITUTE ( [Month], "-Planned", BLANK () ) )
            )
return 
SUMX (
    DISTINCT ( 'Axis'[Legand] ),
    SWITCH (
        [Legand],
        "Late", CALCULATE (
            SUM ( 'export (2)'[late] ),
            t
        ),
        "on Time", CALCULATE (
            SUM ( 'export (2)'[On time t] ),
            t
        ),
        "Planned", CALCULATE (
            SUM ( 'export (2)'[Planned ] ),
            t
        )
    )
)

     


    Best regards,

     

6 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey Dee ,

     

    I have to admit that I do not fully understand what the visual should look like.

    Please prepare a simple drawing and attach an image.

    It is also very helpful if you provide sample data, upload the pbix that contains the sample data (it should also represent your data model) to onedrive or dropbox and then share the link.

    But you can check the bullet chart from OKviz available from the market place.

     

    Regards,

    Tom

    • Dee's avatar
      Dee
      Icon for Helper III rankHelper III
      6 years ago

      TomMartens  I have updated the post, uploaded the pic and the pbix link.

       

      Am trying to see if there's a way I can add maybe two months so that one is independent -Planned and the other two late and on time as stacked, so two months same data.

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        6 years ago
        Hey,

        from my experience this is currently not possible using the default visuals or one of the existing custom visuals.

        But from my experience this should be doable using one http://charticulator.com/

        You might also look to powerbi.tips as they adopted charticulator and provide a different version of charticulator.

        It's easy to export a pbiviz file from charticulator.

        Hopefully this provides some new ideas that will help to tackle this challenge.

        Regards,
        Tom
  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Dee ,

     

    We can also use the axis table as a workaround:

     

    1. create a calculated table and sort by rank column

    Axis = 
UNION (
    CROSSJOIN (
        ADDCOLUMNS (
            SELECTCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "MonthNr", [Value] ),
            "Month", FORMAT ( DATE ( 2000, [MonthNr], 1 ), "MMM" ),
            "Rank", [MonthNr] * 2
        ),
        SELECTCOLUMNS ( { "On Time", "Late" }, "Legand", [Value] )
    ),
    CROSSJOIN (
        ADDCOLUMNS (
            SELECTCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "MonthNr", [Value] ),
            "Month", FORMAT ( DATE ( 2000, [MonthNr], 1 ), "MMM" ) & "-Planned",
            "Rank", [MonthNr] * 2 - 1
        ),
        SELECTCOLUMNS ( { "Planned" }, "Legand", [Value] )
    )
)

     

     

    2. create a measure to show the value

     

    Value = 
Var t = FILTER (
                ALLSELECTED ( 'export (2)' ),
                'export (2)'[Month]
                    IN SELECTCOLUMNS ( 'Axis', "Month", SUBSTITUTE ( [Month], "-Planned", BLANK () ) )
            )
return 
SUMX (
    DISTINCT ( 'Axis'[Legand] ),
    SWITCH (
        [Legand],
        "Late", CALCULATE (
            SUM ( 'export (2)'[late] ),
            t
        ),
        "on Time", CALCULATE (
            SUM ( 'export (2)'[On time t] ),
            t
        ),
        "Planned", CALCULATE (
            SUM ( 'export (2)'[Planned ] ),
            t
        )
    )
)

     


    Best regards,

     

    • Dee's avatar
      Dee
      Icon for Helper III rankHelper III
      6 years ago

      Thank you so much for this!!

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Dee ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,