Forum Discussion

gelsonwirtijr's avatar
gelsonwirtijr
Frequent Visitor
1 year ago

total value different from expected

Hey guys. I'm trying to make a dax measure that returns the sales value of products that are in the aging range "From 91 to 120 days", that is, the sales value of products that have the most recent date in the fInventario table with fInventario[Aging Range] = "From 91 to 120 days". In the image below I have the closest I could get to the expected result. I was able to return the desired products and the sales value, but the Totals are different. Can anyone help me?

 

My measure:

VAR DataMaisRecente =
    CALCULATE(
        MAX(fInventario[Data]),
        ALLEXCEPT(fInventario, fInventario[IdProduto])
    )
VAR VerificaAging =
    CALCULATE(
        COUNTROWS(fInventario),
        FILTER(
            fInventario,
            fInventario[Data] = DataMaisRecente &&
            fInventario[Range de aging] = "De 91 a 120 dias" &&
            [Valor estoque atual] > 0
        ),
        ALLEXCEPT(fInventario, fInventario[IdProduto])
    )
RETURN
IF(VerificaAging > 0, [Vendas brutas], BLANK())



expected result:



pbix is ​​available at:
https://drive.google.com/drive/folders/1LwzYKVU4xDD5KfXCiLVN_c70LT6xG3bc?usp=sharing

Need Help

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    gelsonwirtijr , Try using 

    SalesValueAgingRange =
    VAR DataMaisRecente =
    CALCULATE(
    MAX(fInventario[Data]),
    ALLEXCEPT(fInventario, fInventario[IdProduto])
    )
    VAR VerificaAging =
    CALCULATE(
    COUNTROWS(fInventario),
    FILTER(
    fInventario,
    fInventario[Data] = DataMaisRecente &&
    fInventario[Range de aging] = "De 91 a 120 dias" &&
    [Valor estoque atual] > 0
    ),
    ALLEXCEPT(fInventario, fInventario[IdProduto])
    )
    VAR SalesValue =
    IF(VerificaAging > 0, [Vendas brutas], BLANK())
    RETURN
    SUMX(
    VALUES(fInventario[IdProduto]),
    SalesValue
    )