Forum Discussion

Aditya_Mishra1's avatar
Aditya_Mishra1
Frequent Visitor
2 years ago

Giving wrong value for subcategory level in a Matrix visual

I have been banging my head against this problem for the last week and could really use some assistance, I am working on a inventory aging report, There are Item Groups which are category and there are Products which are the sub category. while calculating the qty for each buckets of age the values that are coming on Item Group Level are correct while the values coming at Product level are wrong. I'm using a matrix visual and the DAX expression that I'm using is

Z_Aging Qty 31-60 Product2 =
VAR SaleSum =
CALCULATE(
SUM('Aging Main'[Sold Qty]),
FILTER('Aging Main', 'Aging Main'[Date] <= MAX(DateTable[Date]))
)
VAR Purch =
CALCULATE(
SUM('Aging Main'[Purchase Qty]),
FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) > 60)
)
VAR PurchSum =
CALCULATE(
SUM('Aging Main'[Purchase Qty]),
FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) > 30 && (MAX(DateTable[Date]) - 'Aging Main'[Date]) <= 60)
)
VAR Sale = -SaleSum // Sold qty had negative values
VAR IsItemLevel = HASONEVALUE('Aging Main'[Item Group])
RETURN
IF(
IsItemLevel,
IF(
Purch >= Sale,
IF(ISBLANK(PurchSum), 0, PurchSum),
VAR SaleSum1 = Sale - Purch
VAR AgingQty =
IF(
SaleSum1 >= PurchSum,
0,
IF(ISBLANK(PurchSum - SaleSum1), 0, PurchSum - SaleSum1)
)
RETURN AgingQty
),
SUMX(
VALUES('Aging Main'[Item Group]),
[Z_Aging Qty 31-60 Product2]
)
)

 

Also in last SUMX expression there comes the error that [Z_Aging Qty 31-60 Product2] cannot be placed here, help me with another DAX expression.

 

The data showing here on the Item Group level is correct whereas

 

The data showing at the Product level is wrong.

Need Help

2 Replies

  • Aditya_Mishra1's avatar
    Aditya_Mishra1
    Frequent Visitor
    2 years ago

    Hi Ritaf1983 

    Actually the data is quite big so I won't be able to share that however I can explain what kind of data I'm working on and what is the issue I'm facing. We have data with Date column, Item group, Product, Purchase qty, sold qty. 
    I'm calculating the age of qty for each Item Group and for their Products as Item group is a category and Products are their sub category as shown in the Screenshots, Base, Chemicals, Concentrate Chemicals are Item group and they have n number of products in them. The problem that I'm facing is I'm getting correct value at the Item level but not for the product level. I'll share the screenshots again as above one's are not clear.

    The values showing here are correct however the values at the product level are wrong.

    The values here in the respective products are wrong.
    A solution was provided to me for the same with this dax

    Zf_Aging Qty <30 =
    VAR SaleSum =
        CALCULATE(
            SUM('Aging Main'[Sold Qty]),
            FILTER('Aging Main', 'Aging Main'[Date] <= MAX(DateTable[Date]))
        )
    VAR Purch =
        CALCULATE(
            SUM('Aging Main'[Purchase Qty]),
            FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) > 30)
        )
    VAR PurchSum =
        CALCULATE(
            SUM('Aging Main'[Purchase Qty]),
            FILTER('Aging Main', (MAX(DateTable[Date]) - 'Aging Main'[Date]) >=0 && (MAX(DateTable[Date]) - 'Aging Main'[Date]) <= 30)
        )
    VAR Sale = -SaleSum // Sold qty had negative values
    VAR IsItemLevel = HASONEVALUE('Aging Main'[Item Group])
    RETURN
    IF(
        IsItemLevel,
        IF(
            Purch >= Sale,
            IF(ISBLANK(PurchSum), 0, PurchSum),
            VAR SaleSum1 = Sale - Purch
            VAR AgingQty =
                IF(
                    SaleSum1 >= PurchSum,
                    0,
                    IF(ISBLANK(PurchSum - SaleSum1), 0, PurchSum - SaleSum1)
                )
            RETURN AgingQty
        ),
        SUMX(
            VALUES('Aging Main'[Item Group]),
            [Zf_Aging Qty <30]
        )
    )
    However at last the SUMX function is not taking [Zf_Aging Qty <30]. Provide me with a solution or If someone has any questions I'll clarify them. Any help or solution would be appreciated