Forum Discussion

Renee_W's avatar
Renee_W
New Member
2 years ago
Solved

Summary Visual from multiple columns

I'm trying to create a summary visual based on data in multiple columns.  Basicly I'd like to create a summary that has two columns - alive/dead and 4 rows - adult, egg,immature, pupae, cysts. 

So that I can create this visual:

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Renee_W ,
    Thank you audreygerred  very much for the solution, and I've tried other ways to help you understand the problem:

    If you want to generate the visual objects you need, you can use the UNION function to regenerate a new table that may help you.

    SummaryTable = 
UNION (
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Adult",
        "Alive", pest_data[Alive Adults],
        "Dead", pest_data[Dead Adults]
    ),
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Egg",
        "Alive", pest_data[Alive Eggs],
        "Dead", pest_data[Dead Eggs]
    ),
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Cyst",
        "Alive", pest_data[Alive Cysts],
        "Dead", pest_data[Dead Cysts]
    )
)
    AliveCount = SUM('SummaryTable'[Alive])
    DeadCount = SUM('SummaryTable'[Dead])

    If you still have questions, I'll upload the Pbix file and hopefully help you out.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Hi! I would change the format of the data itself. Instead of columns like Alive Adult, Dead Adult, Alive Cyst, etc. have a column for Alive/Dead and column for Stage - like below:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Renee_W ,
    Thank you audreygerred  very much for the solution, and I've tried other ways to help you understand the problem:

    If you want to generate the visual objects you need, you can use the UNION function to regenerate a new table that may help you.

    SummaryTable = 
UNION (
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Adult",
        "Alive", pest_data[Alive Adults],
        "Dead", pest_data[Dead Adults]
    ),
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Egg",
        "Alive", pest_data[Alive Eggs],
        "Dead", pest_data[Dead Eggs]
    ),
    SELECTCOLUMNS (
        pest_data,
        "Lifecycle", "Cyst",
        "Alive", pest_data[Alive Cysts],
        "Dead", pest_data[Dead Cysts]
    )
)
    AliveCount = SUM('SummaryTable'[Alive])
    DeadCount = SUM('SummaryTable'[Dead])

    If you still have questions, I'll upload the Pbix file and hopefully help you out.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.