Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Learn more

Reply
BrunoMFerreira
New Member

Issue with Averaging Values Across Filtered Descriptions After Table Merge

‎01-08-2024 04:29 AM

Hello Power BI Community,

I'm encountering a perplexing issue with DAX in Power BI Desktop and could use your collective expertise. My goal is to calculate the average of a column ('DiferençaHorimetro') for each unique description in another column ('Descrição Completa'), regardless of other factors such as material or order number. However, despite applying a filter to only consider a specific description, I'm getting multiple average values within the 'MediaTrocaCol' column for the same description.

Here's a summary of the situation:

  • I have a table with columns: MATERIAL, ORDEM, Descrição Completa, MediaTrocaCol, and DiferençaHorimetro.
  • The 'Descrição Completa' column is a result of merging data from another query.
  • I applied a filter in Power BI to focus on a specific description to test the average calculation.
  • The formula in DAX aims to calculate the average 'DiferençaHorimetro' for each 'Descrição Completa' while excluding zeros.
  • After trying several formula adjustments, including the use of ALLEXCEPT and REMOVEFILTERS, I still end up with multiple averages for the same description.

Here's the latest version of the DAX formula I'm using:

MediaTrocaCol =
VAR DescricaoAtual = TRIM(UPPER('SAP - MB51'[Descrição Completa]))
VAR HorasOperadas = 'SAP - MB51'[DiferençaHorimetro]

VAR MediaDiferenca =
CALCULATE(
AVERAGE('SAP - MB51'[DiferençaHorimetro]),
REMOVEFILTERS('SAP - MB51'),
TRIM(UPPER('SAP - MB51'[Descrição Completa])) = DescricaoAtual,
'SAP - MB51'[DiferençaHorimetro] > 0
)

RETURN
IF(
ISBLANK(HorasOperadas),
BLANK(),
MediaDiferenca
)

Despite the formula and the filter, I'm still seeing various average values in the 'MediaTrocaCol' for what should be the same 'Descrição Completa'. A single average value is expected for each unique description.

I've attached a screenshot for reference.

 

BrunoMFerreira_0-1704716832643.png

 

Does anyone have insights into what might be causing this issue or suggestions on how to resolve it? Any help would be greatly appreciated!

Best regards, Bruno M.

Labels:
Message 1 of 3
240 Views
0
2 REPLIES 2
lbendlin
Super User
Super User

‎01-10-2024 04:34 PM

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 2 of 3
196 Views
0
BrunoMFerreira
New Member
In response to lbendlin

‎01-11-2024 06:06 AM

Here's a breakdown of all the data required to grasp the context. I am attempting to calculate the average using a calculated column in [MediaTrocaCol], but it results in multiple averages for the same [Complete Description]. After filtering out zeros, the average should have been 743.77 for all descriptions, I was only able to include 10 lines. I am also including all calculated columns relevant to this calculation.

[DiferençaHorimetro] represents the difference between the last and the penultimate change of the same description [Complete Description] for the same equipment (same [TAG]). The DAX formula is as follows:

```
DiferençaHorimetro = IF(ISBLANK('SAP - MB51'[HorimetroTrocaPenultima]),0,
'SAP - MB51'[HorimetroTrocaUltima] - 'SAP - MB51'[HorimetroTrocaPenultima])
```

[HorimetroTrocaPenultima] is the hour meter reading for the penultimate change of the equipment [TAG]. The DAX formula is:

```
HorimetroTrocaPenultima =

VAR Description = 'SAP - MB51'[Complete Description]
VAR Equipment = 'SAP - MB51'[TAG]
VAR LastPurchaseDate = 'SAP - MB51'[PreviousChangeDate]

RETURN CALCULATE(
MAX('SAP - IK17'[MEASURED_VALUE]), 'SAP - IK17'[EQUIPMENT] = Equipment, 'SAP - IK17'[NOMINATION]="HOUR METER", 'SAP - IK17'[CREATION_DATE] <= LastPurchaseDate)
```

[HorimetroTrocaUltima] is the hour meter reading for the latest change of the equipment [TAG]. The DAX is:

```
HorimetroTrocaUltima =

VAR Description = 'SAP - MB51'[Complete Description]
VAR Equipment = 'SAP - MB51'[TAG]
VAR LastPurchaseDate = 'SAP - MB51'[ENTRY_DATE]

RETURN CALCULATE(
MAX('SAP - IK17'[MEASURED_VALUE]), 'SAP - IK17'[EQUIPMENT] = Equipment, 'SAP - IK17'[NOMINATION]="HOUR METER", 'SAP - IK17'[CREATION_DATE] <= LastPurchaseDate)
```

[PreviousChangeDate] is the valid date (excluding chargebacks) when the item [Complete Description] was last changed. The DAX is:

```
PreviousChangeDate =

VAR Description = 'SAP - MB51'[Complete Description]
VAR Equipment = 'SAP - MB51'[TAG]
VAR CurrentDate = 'SAP - MB51'[ENTRY_DATE]
VAR FilteredTable =
FILTER(
ALL('SAP - MB51'),
'SAP - MB51'[TAG] = Equipment &&
'SAP - MB51'[Complete Description] = Description &&
'SAP - MB51'[ENTRY_DATE] < CurrentDate
)

VAR PenultimateDate = MAXX(FilteredTable, 'SAP - MB51'[ENTRY_DATE])

RETURN PenultimateDate
``

TAGDATA_ENTRADAOrdemDescrição CompletaDiferençaHorimetroHorimetroTrocaPenultimaHorimetroTrocaUltimaDataTrocaAnteriorMediaTrocaCol
CR-301414/02/2023 00:004447690INJETOR 12V4000 EX59407500020 MTU0  13/01/2023 00:002553
EH-503317/08/2023 00:004520457INJETOR 12V4000 EX59407500020 MTU7619616419624016/08/2023 00:002553
EH-500518/12/2023 00:004534546INJETOR 12V4000 EX59407500020 MTU21220290820312029/08/2022 00:002553
CR-401727/06/2022 00:004413230INJETOR 12V4000 EX59407500020 MTU0 301269 1915
EH-502224/10/2022 00:004442519INJETOR 12V4000 EX59407500020 MTU015866115866112/07/2022 00:001915
EH-500623/11/2022 00:004448702INJETOR 12V4000 EX59407500020 MTU9017461517470521/11/2022 00:001915
CR-401408/07/2023 00:004509081INJETOR 12V4000 EX59407500020 MTU2343328893523210/05/2023 00:001915
 14/06/2022 00:00501982INJETOR 12V4000 EX59407500020 MTU0  26/04/2022 00:001276
 09/01/2023 00:00501982INJETOR 12V4000 EX59407500020 MTU0  02/12/2022 00:001276
CR-400121/01/2022 00:004373769INJETOR 12V4000 EX59407500020 MTU0 136680 1276
CR-305821/02/2022 00:004379342INJETOR 12V4000 EX59407500020 MTU0 107836 1276
EH-600513/04/2022 00:004393294INJETOR 12V4000 EX59407500020 MTU018727418727409/04/2022 00:001276
EQNCUMYA25/09/2022 00:004407050INJETOR 12V4000 EX59407500020 MTU0  06/06/2022 00:001276
EH-502212/07/2022 00:004416705INJETOR 12V4000 EX59407500020 MTU0 105774 1276
CR-402321/09/2022 00:004435130INJETOR 12V4000 EX59407500020 MTU0 166864 1276
CR-308711/10/2022 00:004439264INJETOR 12V4000 EX59407500020 MTU0 167798 1276
EH-500613/10/2022 00:004440280INJETOR 12V4000 EX59407500020 MTU323811208411532223/06/2022 00:001276
CR-400224/10/2022 00:004442463INJETOR 12V4000 EX59407500020 MTU4318765588087613/05/2022 00:001276
EH-500623/11/2022 00:004442649INJETOR 12V4000 EX59407500020 MTU6011641011647021/11/2022 00:001276
CR-307515/11/2022 00:004447322INJETOR 12V4000 EX59407500020 MTU011796011796030/08/2022 00:001276
EH-500228/11/2022 00:004450092INJETOR 12V4000 EX59407500020 MTU011456811456823/11/2022 00:001276
EH-502731/03/2023 00:004478090INJETOR 12V4000 EX59407500020 MTU66438724393828/03/2023 00:001276
EH-502003/04/2023 00:004479397INJETOR 12V4000 EX59407500020 MTU230211458211688428/10/2022 00:001276
EH-600308/04/2023 00:004481278INJETOR 12V4000 EX59407500020 MTU014796614796605/04/2023 00:001276
CR-306406/05/2023 00:004491220INJETOR 12V4000 EX59407500020 MTU109414877214986624/03/2023 00:001276
EH-502409/06/2023 00:004501207INJETOR 12V4000 EX59407500020 MTU104293562946006/06/2023 00:001276
CB-306928/07/2023 00:004514790INJETOR 12V4000 EX59407500020 MTU0 52484 1276
EH-501616/08/2023 00:004518317INJETOR 12V4000 EX59407500020 MTU0 83212 1276
EH-503314/08/2023 00:004519574INJETOR 12V4000 EX59407500020 MTU122977969791809/08/2023 00:001276
EH-600518/08/2023 00:004520965INJETOR 12V4000 EX59407500020 MTU018727418727430/06/2023 00:001276
EH-500619/08/2023 00:004521217INJETOR 12V4000 EX59407500020 MTU011895211895214/06/2023 00:001276
CR-303801/09/2023 00:004525507INJETOR 12V4000 EX59407500020 MTU0 96850 1276
CR-303802/09/2023 00:004525507INJETOR 12V4000 EX59407500020 MTU0968509685001/09/2023 00:001276
EH-502418/12/2023 00:004534564INJETOR 12V4000 EX59407500020 MTU1086330323411809/11/2023 00:001276
EH-503313/10/2023 00:004537877INJETOR 12V4000 EX59407500020 MTU522989429946420/09/2023 00:001276
EH-501021/11/2023 00:004548473INJETOR 12V4000 EX59407500020 MTU9010450810459818/11/2023 00:001276
EH-501705/12/2023 00:004552710INJETOR 12V4000 EX59407500020 MTU96810328112803/12/2023 00:001276
CR-309927/12/2023 00:004558509INJETOR 12V4000 EX59407500020 MTU0 220408 1276
 
Message 3 of 3
189 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All