Forum Discussion
Wrong sub-total with IF function
Hi everybody,
i've a problem with a sub-total of a calculated matrix. I'm starting to say that my english is not so good, so please be patient.
I have a quantity, an average price and an import about the current year.
I want to convert only one category with azienda_id=84 (third row) from dollars to euros. The other two category are already in euros.
So i create a calculated metric, Imp CY:
IF(MAX(VIEW_PBI_AVGI[AZIENDA_ID]) = "84", VALUE(VIEW_PBI_AVGI[Imp_Base CY] / 1.18), VALUE(VIEW_PBI_AVGI[Imp_Base CY]))
How may you see, the sub-total is wronged and i don't understand the problem.
Each single row is correct, only the third row are correctly converted, while the first and second row are not converted.
The sub-total, in this case, is the sum of all rows, but each row is converted, not only the third!
Why? I really don't understand.
Thank you for the support.
Hi,
Please try the below.
If it does not work, please share your sample pbix file, and then I can try to look into it to come up with accurate solution.
New measure: =
SUMX (
VALUES ( tablename[Qty CY] ),
CALCULATE (
IF (
MAX ( VIEW_PBI_AVGI[AZIENDA_ID] ) = "84",
VALUE ( VIEW_PBI_AVGI[Imp_Base CY] / 1.18 ),
VALUE ( VIEW_PBI_AVGI[Imp_Base CY] )
)
)
)
3 Replies
Anonymous
Change "84" to just 84 since it is a number data type . You will get your result ! Share your Kudos!
Hi,
Please try the below.
If it does not work, please share your sample pbix file, and then I can try to look into it to come up with accurate solution.
New measure: =
SUMX (
VALUES ( tablename[Qty CY] ),
CALCULATE (
IF (
MAX ( VIEW_PBI_AVGI[AZIENDA_ID] ) = "84",
VALUE ( VIEW_PBI_AVGI[Imp_Base CY] / 1.18 ),
VALUE ( VIEW_PBI_AVGI[Imp_Base CY] )
)
)
)
