topic Re: Distinct Count of Total Averages in DAX Commands and Tips

Distinct Count of Total Averages

Jozef13 — Wed, 27 Mar 2024 15:27:04 GMT

Hello everyone,
I have a spreadsheet with temperature records from several loggers.
I need to find out how many loggers have an average temperature above a certain value (temperature rounded down without decimals).
I can calculate this using a combination of a pivot table and Excel formulas, but I can't create a calculated field or measure in the pivot table (PowerPivot) that would do this automatically and of course dynamically based on the filters and slicers used, as you can see in the attached file.

Distinct Temp.xlsx

Re: Distinct Count of Total Averages

lbendlin — Thu, 28 Mar 2024 00:06:36 GMT

What is your "certain value" ?

Re: Distinct Count of Total Averages

Jozef13 — Thu, 28 Mar 2024 09:18:13 GMT

The certain value or values is a list with the average temperatures of all loggers rounded down without decimals (integers) in my file labeled as "BaseTemp".
I need to find out how many loggers have an average temperature above 20°C (20<=AVGTemp<21), than 21°C (21<=AVGTemp<22), etc.
Currently I have a pivot table of average temperatures + a helper column of rounded down values, and a table that counts the occurrences of each temperature in the helpere column.
And I want to automate all this and have it in one resulting pivot table.

Re: Distinct Count of Total Averages

lbendlin — Thu, 28 Mar 2024 12:37:59 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Distinct Count of Total Averages

Jozef13 — Thu, 28 Mar 2024 13:32:05 GMT

Hello,

I apologise for my description. I probably expressed myself inaccurately.

Sample data is in linked file "Distinct Temp.xlsx" Sheet name: Data

The expected result is on the "PowerPivot" sheet, where is my actual procedure with the helper column and the resulting FinalTable.
My goal is to create a Pivot table with the same results as in the FinalTable, which will be dynamically recalculated according to the applied filters (slicers).

Distinct Temp.xlsx

Note: a comma is used as the decimal separator in the spreadsheet.

Re: Distinct Count of Total Averages

lbendlin — Thu, 28 Mar 2024 15:50:51 GMT

Power BI does not have a concept of dynamic buckets. You need to bring your own.

Re: Distinct Count of Total Averages

Jozef13 — Tue, 02 Apr 2024 11:45:06 GMT

Your table shows the number of records in the [Sensor No] column that match the list of unique temperatures from the [Temperature (int)] column.
If I change Number of Sensors to Number of Sensors (Distinct), I get the number of sensors with the corresponding temperature, but from the [Temperature (int)] column, where the values are already rounded down.
What I need are the total average temperatures of the individual sensors from the [Temperature (°C)] column, which are then rounded down without decimals, and only from these values I determine the number of sensors (distinct count) whose value is above the relevant temperature (list of unique temperatures in the [Temperature (int)] column

Is it possible to do this using DAX?

Re: Distinct Count of Total Averages

lbendlin — Tue, 02 Apr 2024 16:07:01 GMT

if a sensor is above 17 then it is also above 13, no?

Re: Distinct Count of Total Averages

Jozef13 — Tue, 02 Apr 2024 17:03:49 GMT

Yes, you're right.
But I expect a number of temperature values between integers (i, i+1).
I.e. average temperature above 20°C but less than 21°C (20<=AVGTemp<21),

above 21°C but less than 22°C (21<=AVGTemp<22), etc.

Re: Distinct Count of Total Averages

lbendlin — Tue, 02 Apr 2024 17:33:27 GMT

That's then back to your buckets. Not really sure what you are asking for.

Re: Distinct Count of Total Averages

Jozef13 — Tue, 02 Apr 2024 17:54:59 GMT

I probably can't describe it better than on the "PowerPivot" sheet in my xlsx file.
There is a functional calculation using the pivot table and standard Excel functions.

Distinct Temp_R1.xlsx