Formula incorrect at Total level

[Anu]

Fund.pbix (184.1 KB)

In the attached file, logic for Funding is working fine at month level, but not working in Total level.

I have tried applying this formula using HASONEVALUE.

Funding =
VAR vTable1 =
ADDCOLUMNS (
CALCULATETABLE (
VALUES ( ‘Date’[Year] ),
FILTER ( ‘Funding’, Funding[AdjProvStock LM] + Funding[StockMovement] < 0 )
),
“Value”,
( SUM ( Funding[AdjProvStock LM] ) + SUM ( Funding[StockMovement] ) ) * -1
)
RETURN
IF (
HASONEVALUE ( ‘Date’[MonthOfYear] ),
IF (
[AdjProvStockLM] + SUM ( Funding[StockMovement] ) < 0,
( [AdjProvStockLM] + SUM ( Funding[StockMovement] ) ) * -1
),
SUMX ( vTable1, [Value] )
)

But it is not working at YEAR level ,and TOTAL YEAR level, also the formula should work fine when drilled upwards

[Harsh]

Hello @Anu,

Thank You for posting your query onto the Forum.

Below is the formula provided for the reference in order to correct the totals -

Funding (Correct Totals) = 
SUMX(
    SUMMARIZE(
        Funding , 
        Funding[Item] , 
        'Date'[Date] , 
        "Correct Totals" , 
        [Funding] ) , 
[Correct Totals] )

Also attaching the screenshot as well the PBIX file below for the reference -

I’m also providing a link below where a post was created by one of our expert member @Greg bout how to fix the incorrect totals.

Hoping you find this useful and meets your requirements.

Note: In order to view the entire post, please click onto the link and not onto the “Expand/Collapse” button.

Thanks and Warm Regards,
Harsh

Fund - Harsh.pbix (185.1 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[Anu]

Thanks Harsh !

But the funding for April 2019 should be 3 .
Net Movement in Apr = AdjProvStockLM + StockMovement =17+ ( -20) = -3
So the funding should be -3 x -1 =3
So that NetMovement is not negative.
What is meant by funding is any additional funds added, so that net movement is not negative. Here, the netmovement for Apr 2019 was -3, so a funding of 3 is required to make it non-Negative
Similarly for Aug 2020, AdjProvStockLM + StockMovement = 1 +(-4) = -3 again
So Funding is -3 x-1= 3
A funding of 3 should be added to make the net movement non-negative

Expected Result:

Also the drill ups should work fine.

[Harsh]

Hello @Anu,

Well, I just wrote 2 simple formula’s firstly to calculate the figures of individual line items and then to calculate the correct totals. Below are the formulas provided for the reference -

Funding - Harsh = 
IF (
    ( [AdjProvStockLM] + [Stock Movement] ) < 0,
    ( [AdjProvStockLM] + [Stock Movement] ) * -1 , 
    BLANK() )



Funding (Correct Totals) = 
SUMX(
    SUMMARIZE(
        'Date' , 
        'Date'[Date] , 
        "Correct Total" , 
        [Funding - Harsh] ) , 
    [Correct Total]
)

Below is the screenshot provided of the final result. It shows the correct results whether drilled downwards or upwards -

Would like to know your thoughts about why this long formula was written? As given below -

Funding - 1 = 
VAR vTable1 =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Date'[Year] ),
            FILTER ( 'Funding', Funding[AdjProvStock] + Funding[StockMovement] < 0 )
        ),
        "Value",
            ( SUM( Funding[AdjProvStock] ) + SUM ( Funding[StockMovement] ) ) * -1
    )

RETURN
    IF (
        HASONEVALUE ( 'Date'[MonthOfYear] ),
        IF (
            [AdjProvStockLM] + SUM ( Funding[StockMovement] ) < 0,
            ( [AdjProvStockLM] + SUM ( Funding[StockMovement] ) ) * -1
        ),
        SUMX( vTable1 , [Value] 
        )
    )

But anyways, I’m attaching the working of my PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’re looking for.

Note: As shown in the link provided above, I’ve followed the “Measure Branching Technique” to calculate the correct totals. Since I like the calculations happening step-by-step rather than it happening virtually (In terms of “Variable Technique”).

Thanks and Warm Regards,
Harsh

Fund - Harsh v2.pbix (184.6 KB)