Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
3 years ago
Solved

Chart Help

Hi there ,    I have a data as below ;   When i create a chart on power bi , datas are showed together based on months as below     But i want to show datas as below based on their...
Need Help
  • SeanMcLarty's avatar
    SeanMcLarty
    3 years ago

    I was hoping Field Parameters could be used to quickly solve this but the result wasn't quite what you requested.  Instead I put your Categories into a table and added a measure to extract the values from your table.

    1. Category table (DAX), Seq should be used as sorting column:

    Categories = DATATABLE(
        "Category", STRING,
        "Seq", INTEGER,
        {
            {"Total Sales", 1},
            {"Branch 1 Sales", 2},
            {"Branch 2 Sales", 3},
            {"Total Order Qty", 4},
            {"Total Delivery", 5},
            {"File Qty", 6}
        }
    )

    2. Measure (DAX):

    Measure = SWITCH(SELECTEDVALUE(Categories[Category]),
        "Total Sales", SUM('Table'[Total Sales]),
        "Branch 1 Sales", SUM('Table'[Branch 1 Sales]),
        "Branch 2 Sales", SUM('Table'[Branch 2 Sales]),
        "Total Order Qty", SUM('Table'[Total Order Qty]),
        "Total Delivery", SUM('Table'[Total Delivery]),
        "File Qty", SUM('Table'[File Qty])
    )

    3. Use Clustered Column Chart visual with 'Categories'[Category] as x-axis, 'Categories'[Measure] as y-axis and 'Table'[Month] as legend.  

SeanMcLarty's avatar
SeanMcLarty
Advocate I
3 years ago

I was hoping Field Parameters could be used to quickly solve this but the result wasn't quite what you requested.  Instead I put your Categories into a table and added a measure to extract the values from your table.

1. Category table (DAX), Seq should be used as sorting column:

Categories = DATATABLE(
    "Category", STRING,
    "Seq", INTEGER,
    {
        {"Total Sales", 1},
        {"Branch 1 Sales", 2},
        {"Branch 2 Sales", 3},
        {"Total Order Qty", 4},
        {"Total Delivery", 5},
        {"File Qty", 6}
    }
)

2. Measure (DAX):

Measure = SWITCH(SELECTEDVALUE(Categories[Category]),
    "Total Sales", SUM('Table'[Total Sales]),
    "Branch 1 Sales", SUM('Table'[Branch 1 Sales]),
    "Branch 2 Sales", SUM('Table'[Branch 2 Sales]),
    "Total Order Qty", SUM('Table'[Total Order Qty]),
    "Total Delivery", SUM('Table'[Total Delivery]),
    "File Qty", SUM('Table'[File Qty])
)

3. Use Clustered Column Chart visual with 'Categories'[Category] as x-axis, 'Categories'[Measure] as y-axis and 'Table'[Month] as legend.