table visual with multiple values

[pasqualino]

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 2nd table below (The Average is exactly the average of daily entries during the selected month; Max is the maximum daily entries for the selected month.)

I tried with Summarizing categories (one summary for OS, another for Role) and achieved AVG, but then I am not able to put them together in one single table. On the Max calculation I am completely lost. I am totally newbie

Date	OS	Role
12 march 2024	linux	oracle
13 march 2024	windows	oracle
14 march 2024	windows	oracle
14 march 2024	linux	oracle
14 march 2024	linux	sql
1 april 2024	linux	sql
1 april 2024	windows	sql
1 april 2024	linux	sql
1 april 2024	windows	sql
1 april 2024	linux	sql
1 april 2024	windows	sql
1 april 2024	linux	oracle
02-may-24	linux	oracle
02-may-24	windows	oracle

Month	Average	Max
Linux
Windows
Oracle
SQL
ALL servers

[v-hjannapu]

Hi @pasqualino,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and Accept it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Regards ,
Harshitha.

[Ashish_Excel]

Hi,

The very first action you must perform on your dataset is "Unpivot Other Columns" in Power Query.  For help on the result, for the sample table that you have shared, show the expected result.

[m4ni · Accepted solution]

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.

[pasqualino]

Thanks a lot @m4ni I succeeded in creating date table, I am now struggling with the Daily count as it is returning the same value if I filter for Oracle, Sql or any other value I have in my tables 😕

[m4ni]

Hi @pasqualino 

The reason for incorrect values is likely because you dont have a unique key in your table therefore the records are getting grouped.  Please try these steps:

1. Go into Power Query and add an Index column from 1

2. Amend the Count Per Day measure to now summarize by Index rather than OS.

This should give you correct results as you now have uniqueness due to Index.

Please confirm how you wish to display the results.  If you want to display the max value in a seperate card visual then you need to filter Count Per Day measure for either "sql", "oracle" or other values.

If you want to display the max value in a table then you can take out the filter on role.

Screenshot should help make sense...

[pasqualino]

Could you please help me with the date table and join? I already have date on each data table row, is it a must to have a separate table?

[m4ni]

The date table is a must, yes. Firstly its best practice and secondly, in your example using the data table gives wrong results (I tested that), because you dont have unique values.

However if you want to know the workaround, please amend the second measure to:

MAX Value = MAXX(VALUES(Sheet1[Date]), [Count Per Day])

That will give you correct numbers.  Assuming the numbers are correct of course.

The date table can be created using the CALENDARAUTO function in DAX.

1. New table > CALENDARAUTO()

2. Add date columns as needed.

[m4ni]

I feel you need to re-evaluate what exactly you are trying to measure.  What is it you are trying to average?  What is it you are trying to find the MAX of?

Your description tells me it is counts of enteries per day but summarized by month.  Worth just clarifying..

[pasqualino]

hello @m4ni my apologies for the unclear explanation. Yes, I want to count all the appearances of Oracle, for instance and return: how many oracle there were on average on the chosen month? What was the highest volume (daily based) on a selected month?

[burakkaragoz]

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]

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]

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