row total Problem for Aging Report

[Aditya_Mishra1]

Z_Aging Qty 31-60 =

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

RETURN

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

)

This DAX is used to find the quantity of Products in the bucket 31-60 days, and it's giving the right values. However the data that we have is Item Group as category and Products as sub category, for products it showing the data right but for each Item group the addition of the products value are not adding up properly, and the sum of all the values in item group is not adding properly either. note that I'm using a matrix visual and data for each Product is coming fine but for each Item group it's giving false values and if we maximise the Item group the values in the products are coming fine, what can be the issue here and give a dax which fixes the issue.

[Aditya_Mishra1]

Apologies for the confusion but I would like to correct myself, the values that I'm getting at Item Group level is correct and the values that are coming in Product level are wrong and total sum of rows at the end is also coming wrong though the Item level values are correct. More details about the type of data I have is that Item Group is category and Product are the subcategory, sharing an screenshot for better understanding

In the screenshot above the data that is coming on Item Group level is correct but the Total at the bottom is showing wrong values,

And the data coming at Product level is wrong, sharing my DAX expression again.

Z_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

RETURN

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

)

[Anonymous]

Hi@ @Aditya_Mishra1 ，

Seems to be a problem with the wrong TOTAL value? Please try to create another meaure based on [Z_Aging Qty <30].

result =
VAR _b =
    SUMMARIZE ( 'table', 'table'[item group], "aaa", 'table'[Z_Aging Qty <30] ) //You can change [Item Group] to another value that is unique to the table, such as date column, index, and so on.
RETURN
    IF (
        HASONEVALUE ( 'table'[item group] ),
        [Z_Aging Qty <30],
        SUMX ( _b, [aaa] )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Aditya_Mishra1 ,

It seems that the issue you're experiencing is related to the context in which the DAX calculations are being performed, especially when aggregating data at the Item Group level.

In Power BI, when using a matrix visual, the calculations for subtotals and grand totals can sometimes behave differently than expected because they are calculated based on the current context, which includes all filters and slicers that are applied to the visual.

To address this issue, you may need to modify your DAX measure to ensure that it calculates correctly at all levels of aggregation. One common approach is to use the `HASONEVALUE` function to determine if the calculation is being done at the product level or at a higher level of aggregation, such as the Item Group level, and then adjust the calculation accordingly.

Z_Aging Qty 31-60 = 
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 IsProductLevel = HASONEVALUE('Product'[ProductName])
RETURN
IF(
    IsProductLevel,
    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('Product'[ProductName]),
        [Z_Aging Qty 31-60]
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aditya_Mishra1 · Accepted solution]

Hi @Anonymous 

After applying the DAX you provided, the Total at the bottom is summing up the values of Item group which has been correct so the Total is showing right values, however the values at Product level is still the same after I changed your DAX from Product level to Item level.

Also at last in SUMX function, it's giving error to pass same value as the current measure name.

I modified the DAX you provided to this

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]

    )

)