Forum Discussion

pasqualino's avatar
pasqualino
Frequent Visitor
1 year ago
Solved

table visual with multiple values

Hello, I finally achieved a decent merge of source files into one table, which looks similar to the following table. Each row represent 1 server. I need to produce a table showing something like the ...
Tips and Tricks
  • m4ni's avatar
    m4ni
    1 year ago

    Hi,

    OK please try the following.

     

    1. Create a date table and join to the data table for filtering.

    2. Add a month column to the date table using the FORMAT function.  Add month as slicer.

    3. Create 1st measure : (For ORACLE ONLY)

    Count Per Day =
    Var Result = CALCULATE(COUNTX(SUMMARIZE(Sheet1, Sheet1[Date], Sheet1[OS]), 1),FILTER(Sheet1, Sheet1[Role] = "oracle"))
    RETURN Result

    4. Create 2nd measure:

    MAX Value = MAXX('date', [Count Per Day])

     

    So using March and Oracle in your example gives me 2 because there are 2 dates with "Oracle" and thats the highest number.  See screenshot.

     

    Hope this helps.  Let me know either way.

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi pasqualino ,

Great job on merging your data so far! To achieve a table that shows both the Average and Max values by OS (or Role), you’ll need to use measures in Power BI and display them together in a matrix or table visual. Here’s a simple step-by-step guide:

1. Create Measures for Average and Max

Go to your table in Power BI and create two new measures:

Average:

DAX
 
Average Value = AVERAGE('YourTable'[ValueColumn])

Max:

DAX
 
Max Value = MAX('YourTable'[ValueColumn])

Replace [ValueColumn] with the column you want to aggregate (e.g., daily entries/count).

2. Use a Matrix or Table Visual

  • Add a Matrix visual to your report.
  • Drag the OS (or Role) field to the Rows area.
  • Drag your new Average Value and Max Value measures to the Values area.

This will create a table with each OS (or Role) and display both Average and Max for each.

3. For Monthly Aggregation

If you want to see these by month:

  • Add your date field to the Columns or Rows and use Power BI’s Date Hierarchy to group by Month.
  • Or, create a new column for the month (e.g., Month = FORMAT([Date], "mmm-yyyy")) and use that in your visual.

4. Combine All Servers

To get an "ALL servers" row, the matrix/table visual will automatically aggregate across all OS/roles (the Grand Total).

Summary Steps

  1. Create the necessary measures (Average, Max).
  2. Use a Matrix/Table visual to display OS/Role on rows and your measures as columns.
  3. Add the Month grouping if needed.

If you need help with the exact DAX or have a specific column name, just let me know! Screenshots or your sample data are always welcome.

You’re on the right track—let me know if you need a step-by-step with screenshots!
translation and formatting supported by AI

  • pasqualino's avatar
    pasqualino
    Frequent Visitor
    1 year ago

    thanks a lot burakkaragoz I think I succeeded with AVG, I am, on the other side, struggling with MAX calculation.

    I have added a column for each of the categories I am exploring, setting it to 1 or 0. 

    I set measures to 

    Max DB2 in a month = MAX(Consulta1[Count DB2]) but it is always showing the same value, not caring about the date filtering 😭
    • v-hjannapu's avatar
      v-hjannapu
      Community Support
      1 year ago

      Hi pasqualino,
      Thank you burakkaragoz , for your reply regarding query.

      Using CALCULATE ensures it respects the filters from your visual, like slicers or matrix rows/columns for month or category. Also, make sure you're using a proper Date field from a calendar table to slice the data by month.
      Try updating your measure like this:

      Max DB2 in a Month = 
CALCULATE(
    MAX(Consulta1[Count DB2])
)


      please find the attached screenshot and pbix file for your reference

      If I misunderstand your needs or you still have problems on it, please feel free to let us know.  
      If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

      Best Regards,
      Harshitha.
      Community Support Team