Forum Discussion

BrunoMFerreira's avatar
BrunoMFerreira
New Member
2 years ago

Issue with Averaging Values Across Filtered Descriptions After Table Merge

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.

 

 

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.

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

    • BrunoMFerreira's avatar
      BrunoMFerreira
      New Member
      2 years ago

      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