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, max, avg, and stdev "totals" on the bottom.  The data looks like the following:

 

[ID, Data1, Data2;

1, 334, 132;

2, 337,172]

 

I want to get the following in the visualization on my report

 

[ID, Data1, Data2;

1, 334, 132;

2, 338,142;

Avg, 336, 137;

Min, 334, 132;

Max, 338, 142;

STDEV, ?, ?]

  • 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

3 Replies

  • dilumd's avatar
    dilumd
    Impactful Individual
    8 years ago
    Hi

    How's your data looks like? Can you give some sample data and final expected result?
    • 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
        Microsoft 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