topic Re: measure with function like TRIMMEAN of excel in DAX Commands and Tips

measure with function like TRIMMEAN of excel

DSchmndtk — Thu, 25 Apr 2024 05:13:37 GMT

Hello I am looking for a code like the trimmean function in excel.
The previvous posts i checked didn't help me finding a solution.
Maybe someone can help me with an kind of sample code and what i have to replave inside of the code...

Re: measure with function like TRIMMEAN of excel

123abc — Thu, 25 Apr 2024 06:43:08 GMT

In Power BI, you can replicate the TRIMMEAN function using DAX (Data Analysis Expressions). Below is a DAX formula to calculate the trimmean:

TrimMean =
VAR Data = SUMMARIZE('Table', 'Table'[Value])
VAR Percent = 0.2 -- Change this to the desired percentage
VAR Count = COUNTROWS(Data)
VAR TrimCount = INT(Count * Percent)
RETURN
AVERAGEX(
TOPN(Count - 2 * TrimCount, Data, 'Table'[Value], ASC),
'Table'[Value]
)

Here's what you need to do:

Replace 'Table' with the name of your table.
Replace 'Table'[Value] with the column that contains the values you want to calculate the trimmean for.
Change the Percent variable to the desired percentage.

Here's a step-by-step guide:

Open your Power BI Desktop.
In the "Modeling" tab, click on "New Measure".
Paste the DAX formula provided above and adjust it according to your data.

For example, if you have a table named Sales and a column named Amount, your DAX formula would look like this:

TrimMean =
VAR Data = SUMMARIZE('Sales', 'Sales'[Amount])
VAR Percent = 0.2 -- Change this to the desired percentage
VAR Count = COUNTROWS(Data)
VAR TrimCount = INT(Count * Percent)
RETURN
AVERAGEX(
TOPN(Count - 2 * TrimCount, Data, 'Sales'[Amount], ASC),
'Sales'[Amount]
)

This DAX formula creates a new measure named TrimMean that calculates the trimmean of the Amount column in the Sales table, removing 20% of the data from the top and bottom tails.

You can then use this TrimMean measure in your reports and visuals just like any other measure.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: measure with function like TRIMMEAN of excel

Anonymous — Mon, 29 Apr 2024 08:33:23 GMT

Hi DSchmndtk ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a Rank column

Rank = RANKX('Table',[Value],,ASC)

Create a measure

Trimmed Mean = VAR _percentage = 0.3 VAR _startRank = FLOOR(COUNTROWS('Table')*_percentage/2+1,1) VAR _endRank = COUNTROWS('Table')*(1-FLOOR(_percentage/2,0.1)) VAR _sum = CALCULATE( SUM('Table'[Value]), FILTER( 'Table', 'Table'[Rank] <=_endRank && 'Table'[Rank] >= _startRank ) ) VAR _count = COUNTROWS('Table')*(1-FLOOR(_percentage/2,0.1)*2) RETURN _sum/_count

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly