Forum Discussion

mberlinger3's avatar
mberlinger3
Frequent Visitor
8 years ago
Solved

Need min, max average, and st dev below table visualization

Hey guys,   Im trying to get my company to move to powerbi but i need to make templates as similar as possible to the former excel templates.  How do I make a simple table visualization with min, m...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi mberlinger3,

    For your requirement, you need to create a new table, then create the expected report using the new table.

    I use your first sample table as screenshot1 shown. Click "New Table" under Modeling on home page, type the following formula, you will get a new table as the screenshot2 shown.

    screenshot1

    NewTable =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Data1", Table1[Date1],
        "Data", Table1[Date2],
        "ID", Table1[ID]
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "Avg1", AVERAGE ( Table1[Date1] ),
            "Avg2", AVERAGE ( Table1[Date2] )
        ),
        "Avg", "Avg"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "min1", MIN ( Table1[Date1] ),
            "min2", MIN ( Table1[Date2] )
        ),
        "min", "min"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "max1", MIN ( Table1[Date1] ),
            "max2", MIN ( Table1[Date2] )
        ),
        "max", "max"
    )
)



    Then you can create a table, select all the columns in new table to display.



    Please download the attachment file to check more information.

    Best Regards,
    Angelia

mberlinger3's avatar
mberlinger3
Frequent Visitor
8 years ago

Dilumd,

 

I gave a demo above.  I am using the table visualization but I am limited to just a total row.  I would like 4 "Total" rows one for each of the metrics "min, max, avg, stdev"

 

Mike

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi mberlinger3,

For your requirement, you need to create a new table, then create the expected report using the new table.

I use your first sample table as screenshot1 shown. Click "New Table" under Modeling on home page, type the following formula, you will get a new table as the screenshot2 shown.

screenshot1

NewTable =
UNION (
    SELECTCOLUMNS (
        Table1,
        "Data1", Table1[Date1],
        "Data", Table1[Date2],
        "ID", Table1[ID]
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "Avg1", AVERAGE ( Table1[Date1] ),
            "Avg2", AVERAGE ( Table1[Date2] )
        ),
        "Avg", "Avg"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "min1", MIN ( Table1[Date1] ),
            "min2", MIN ( Table1[Date2] )
        ),
        "min", "min"
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            "max1", MIN ( Table1[Date1] ),
            "max2", MIN ( Table1[Date2] )
        ),
        "max", "max"
    )
)



Then you can create a table, select all the columns in new table to display.



Please download the attachment file to check more information.

Best Regards,
Angelia