Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

DAX. Projections appear empty or incorrect. Attachment pbix

Good.

I attach an image and comment.

I have an array made up of codes and when displaying its date, each date has a "IMPORTE_POSICION" with a value and in the event that the position is approved, an amount is indicated in "SIGNED". I need to create a simple projection with these two conditions:
If "SIGNED" is different from €0.00, enter the amount "SIGNED".

If "SIGNED" is equal to €0.00, put the average of the "SIGNED"

If "SIGNED" is empty, put "IMPORTE_POSICION"

I've tried several measurements, but I can't get what I want, the two that appear are:

Projection =
WERE SignedAmount =
CALCULATE(
SUM('data'[importe_posicion]),
'data'[signed] <> 0,
VALUES('data'[signed])
)
WERE AverageSignedZero =
CALCULATE(
AVERAGE('data'[importe_posicion]),
'data'[signed] = 0
)
RETURN
IF(
AmountSigned <> BLANK(),
AmountSigned,
AverageSignedZero
)
---------
I've been calculating step by step to check that everything is correct but I can't do it with these three measures either:
SignedAmount =
SUMX(
VALUES('Data'[code]),
CALCULATE(
SUM('Data'[importe_posicion]),
'Data'[signed] <> 0,
VALUES('Data'[signed])
)
)
AverageSignedZero =
CALCULATE(
AVERAGE('Data'[importe_posicion]),
'Data'[signed] <> 0
)
Projection2 =
CALCULATE (
IF (
[SignedAmount] <> 0,
[SignedAmount],
IF (
[SignedAmount] = 0,
[AverageSignedZero]
)
)
)

I'm attaching the file, in case someone can explain to me, because I don't get the correct result, I put an image of how it would be done in excel:

https://www.dropbox.com/scl/fi/pd0xvwtfruoaqbuqm0w6d/proyeccion.zip?rlkey=ucnx92snxjip4dixc2ze40omo&dl=0

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI Syndicate_Admin,

    Measure expressions are calculated based on its row and filter contents. So if you want to invoke them in other formula, you need to reproduce the current row contents group and filter effects.

    You can try to create a variable table with summarize function to reproduce the current row contents.

    Measure Totals, The Final Word 

    Regards,

    Xiaoxin Sheng

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      Buenas Xiaoxin Sheng.

      Thank you very much for your reply. Could you give me an example?

      Best regards