Calculating Min,Max,Ave of a Measure

[roncruiser]

The column based values shown, below, are based on calculated measures by column.

The table captured below is in this file along with each calculated measure.

File: https://drive.google.com/open?id=1rz8B_COx_boHI9k7Jhs8kq9R9zaBMJgw

There are five different calculated measures:

1. setupDCD
2. setupWidth
3. passingWidth
4. holdWidth
5. holdDCD

There are eight values for each measure per column.

I can show more or less per column based on the filter settings.

What I simply need is the min, max, average for each measure per column.  No matter how I filter the data.
I've played with minx, maxx, averagex and summary with no luck yet.  This should be straight forward, but no so.

Within the context of DAX, there must be a way to save each column as a separate table from which min, max and average are derived.

Looking for help...  thank you....

[v-chuncz-msft · Accepted solution]

@roncruiser 

You may try adding SUMMARIZE.

• https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...
• https://community.powerbi.com/t5/Desktop/monthly-average-of-daily-sums/m-p/291451#M128396

[roncruiser]

@v-chuncz-msft 

Summarize worked well, but only partially.  I need on the fly analysis for min, max and average of each measure based on the values displayed which are based on the column filter.  I can reduce or increase the granularity of the displayed values of each measure based on the filter settings.  From which I need to view the min, max and average of what is displayed.

Currently, based on your great help, I am hardcoding the filter for each measure.  

5 measures just to get minimum width.  Then another 5 measures for max width...

Analysis quickly became tedious.

Min Width per Device =minX (
SUMMARIZE ( raw_data,raw_data[Vref], raw_data[SN],raw_data[RD_WR],raw_data[Vendor]),
[passingWidth]
)

Min Width per Bit =minX (
SUMMARIZE ( raw_data,raw_data[Vref], raw_data[SN],raw_data[RD_WR],raw_data[Vendor],raw_data[Rank],raw_data[Byte],raw_data[Ch],raw_data[Bit]),
[passingWidth]
)

Min Width per Byte =minX (
SUMMARIZE ( raw_data,raw_data[Vref], raw_data[SN],raw_data[RD_WR],raw_data[Vendor],raw_data[Rank],raw_data[Byte],raw_data[Ch]),
[passingWidth]
)

Min Width per Ch =minX (
SUMMARIZE ( raw_data,raw_data[Vref], raw_data[SN],raw_data[RD_WR],raw_data[Vendor],raw_data[Rank],raw_data[Ch]),
[passingWidth]
)

Min Width per Rank =minX (
SUMMARIZE ( raw_data,raw_data[Vref], raw_data[SN],raw_data[RD_WR],raw_data[Vendor],raw_data[Rank]),
[passingWidth]
)

I need a way to write the measure to min, max, etc on the fly as the filter is changed.

Hope you can help... I'll keep trying on my side!

Thanks for everything!

[Clinical_Epi]

Hi There,

Been some time since you posted this, but hopefully this may help someone else also.

I'm trying to do the same - Min and Max of an Average - which itself is a measure, that is comprised of granularity. Most of my work ins on Patients in Hospitals, so granularity and automattion of the dashboard s essential.

Assuming we have worked out how to do the Min/Max calculation, which as you say is tedious, we need to be able to use it to filter in =various ways (for granularity and comparison lets say).

What I do once things get over the top is create a measure that captures everything I need, then use that in specific measures like Month to date, Last month and previous period comparisons = I do this so filtering can continue unhampered.

SO: once we have all our measures, I will do the following.

1) Create a static table to handle the measure names (they may be lines in a chart lets say)

[note: its important to keep an eye ontable references as sometimes PBi will reference our new table to other ones int he dashboard - we dont want this new tbl related to anything]

2) Then create a measure to handle the filtering selection from our new table:

ED Admit Status SELECT = SELECTEDVALUE('my_tbl'[admission_selection])

3) Next we create a switch to handle the selection, 

I should meantion I tend to pull in more filters that comprise of the same code as below, this way my 'specific' measure based filtering can be expanded to some extent without error.

%ED LOS ≥ 24hrs filter =

            SWITCH('ED SQL Data'[ED Admit Status SELECT],

                "Admissions",('ED SQL Data'[% ED LOS ≥ 24hr Admitted]),

                "Non-admissions",CALCULATE('ED SQL Data'[%ED LOS ≥ 24hr Non-admitted]),

                "Total",CALCULATE('ED SQL Data'[LOS ≥ 24hrs]/COUNT('ED SQL Data'[ur_number]))

            )

notes: in the above expression we are saying "when we use a page filter 'admission status' display the following measures... (in a nut shell)

4) Final measure for filtering in any page based graph/card etc. as it can be re-used in any of these (tables/graphs/cards) - always making sure to format correctly (precentage or decimal etc..)

I'm adding these for anyone new to pbi (not your good self 🙂 ).

Card month to date: 

% ED LOS ≥ 24hrs (MTD) = TOTALMTD('ED SQL Data'[%ED LOS ≥ 24hrs filter],'_Date'[Date filter])

Card year to date:

%ED LOS ≥ 24hrs (YTD) = TOTALYTD('ED SQL Data'[%ED LOS ≥ 24hrs filter],'_Date'[Date filter])

Card last month to date:

%ED LOS ≥ 24hrs (Prev MTD) = CALCULATE('ED SQL Data'[%ED LOS ≥ 24hrs filter], PREVIOUSMONTH(DATESMTD('_Date'[Date filter])))

Same period previous year:

CALCULATE('ED SQL Data'[% ED LOS ≥ 24hrs (MTD)],SAMEPERIODLASTYEAR(_Date[Date filter]))

Then I use the Month to date and previous period measure for the chart - so they are essentially month calculations. I do use a simple dunamic date table as am not interested in financial periods. Also again I automate dashbaords, so mainly use SQL for data and refresh dashboards daily.

Hope that helps somewhat with your own adventures with Power BI 🙂

Kind regards,

Clin Epi

PS: I'll come back here and post my own MIN/MAX expressions if I ever sort it out..

[roncruiser]

@v-chuncz-msft 

Thanks!  I will give this a try now.  I may need your help...  🙂

[roncruiser]

@v-chuncz-msft 

Summarize works at a very high level, but is there a way to show sumx, minx, maxx, averagex based on the filter setting of the table itself.  So, that way the valueX will show dynamically based on the filtering of the column?

Using MinX as example, if I filter out rows of a column, I should get value MinX of only the rows that are displayed for each column.

Thank you...

...