1

I'm trying to create a measure that returns a Sum or an Average, depeding on the value of a column. I understand that you need to give a row context to the expression, but I don't like the idea of doing and averege on an id column This is my code:

ValorHandle = IF (EVENTOS_DELTA_HD[idGlo] = 11,SUMX(EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),FORMAT(AVERAGEX (EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),"Percent"))

This throws this error:

A single value for column ‘idGlo’ in table ‘EVENTOS_DELTA_HD’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

If I do something like

ValorHandle = IF (AVERAGE(EVENTOS_DELTA_HD[idGlo]) = 11,SUM(EVENTOS_DELTA_HD[Valor]),FORMAT(AVERAGE (EVENTOS_DELTA_HD[Valor]),"Percent"))

the error goes away, but is just makes no sense doing the average of an id field. This would just break if the ID was not numerical.

Any idea on how to fix this? idGlo, just tells me the type of calculation I need to apply to my "Valor" column (SUM or AVERAGE). Also even when using average(idGlo), measure is not correct, when table relationships are involved

Let me add that I cannot do 2 different measures, because I need to show averages and sums in the same report elements

Mixing different calculations

In this example, if the "Nombre" column come from a related table, the report just breaks, as it does not respect the relation between "HANDLE" and "Nombre" when adding the measure

Jack Casas
  • 914
  • 18
  • 37
  • Hello Jack. Could you please share some data and your data columns? – Ozan Sen Aug 01 '22 at 10:04
  • Data is a very simple "Values" table, where Handle column is de ID field, Valor is a decimal number, and idGLo is the Handle Type., and EventDate is a datetime field. When idGlo is 11, the Pbi measure should show SUM(Valor), and else it should show AVERAGE(Valor). I have another table "Handles "where I get the Handle Name with a simple relation by Handle ID. That's it. – Jack Casas Aug 01 '22 at 10:50
  • what is an idea of this expression `EVENTOS_DELTA_HD[idGlo] = 11` ? – Mik Aug 01 '22 at 11:11
  • it's the condition.. when idGlo is 11, I need to show the SUM, and in other cases, the AVERAGE. If you look the screenshot in my post, the percent values are averages, and the decimal ones are sums. If for example I filter year 2022, month 7, I need my measure to show the correct calculation, based on idGlo, avg or sum – Jack Casas Aug 01 '22 at 12:47
  • 1
    did you try `SELECTEDVALUE(EVENTOS_DELTA_HD[idGlo])=11` ? – Mik Aug 01 '22 at 13:17
  • @Mik ´´´´SELECTEDVALUE()´´´´ actually worked! Please post a response to mark it as solution. Thanks. – Jack Casas Aug 01 '22 at 13:57

1 Answers1

1

Change AVERAGE(EVENTOS_DELTA_HD[idGlo]) = 11 with SELECTEDVALUE(EVENTOS_DELTA_HD[idGlo])=11

Mik
  • 2,099
  • 1
  • 5
  • 19
  • Actually it's ````ValorHandle = IF (SELECTEDVALUE(EVENTOS_DELTA_HD[idGlo] )= 11,SUMX(EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),FORMAT(AVERAGEX (EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),"Percent"))````, a modification of my first formula – Jack Casas Aug 01 '22 at 14:20