Forum Discussion

Aditya_Mishra1's avatar
Aditya_Mishra1
Frequent Visitor
2 years ago
Solved

row total Problem for Aging Report

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...
Need Help
  • Aditya_Mishra1's avatar
    Aditya_Mishra1
    2 years ago

    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]
        )
    )
Aditya_Mishra1's avatar
Aditya_Mishra1
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.