Summary Visual from multiple columns

[Renee_W]

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!

[Anonymous · Accepted solution]

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.

[audreygerred]

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: