Issues with Totals

Power BI
1

Please I am having issues with Totals in a Matrix Visual

What I am trying to do is quite complex.

So within the Matrix Table, I have a field called ‘Projected’

For Projected Work Orders, I want to net off the budgets under Open and Initiated WO status till it gets to 0.

This new Projected Budget is then what I want to used in calculating my grand total…

In essence within my measure there is an override on what to do when the WO Status is Projected.

I now want that new Projected to be what is used in the total not the old version

2

Would it be this? If not, can you send a sample of your data along with an example?
The data may be fictitious

Medida = 
IF( 
    ISINSCOPE( Tabela[Work Order] ),
    SUM( Tabela[Budget] ),
    CALCULATE( SUM( Tabela[Budget] ), Tabela[WO Status] = "Projected"
    )
)

image

3

Hi Thanks for your response. This is what the data looks like

image

This is the measure I am currently using

My objective is to calculate the total budget associated with each Workorder status however for Projected Workorders, the budget for Open and Initiated should be netted off till it gets to 0. Meaning the Budget measure would need to incorporate an adjustment for Projected Workorders

I have been able to do this successfully but the grand total does not work.

For the grand total, I am still getting a sum that reflect the original not adjusted projected budget regardless of the logic built into the measure

Below is my measure. When I use ‘HASONEVALUE’ I get 0 as the grand total

Please help

Budget =

VAR WOstatus =

FIRSTNONBLANK ( 'Projected WO'[WO_Status], 1 )

VAR BudgetTotal =

CALCULATE (

    SUM ( Budget[Amount] ),

    FILTER ( Budget, Budget[BudgetType] = "Budget" )

)

VAR ProjectedAmount =

CALCULATE (

    BudgetTotal,

    'Projected WO'[WO_Status] IN { "Projected", "projected" }

)

VAR InitiatedAmount =

CALCULATE (

    BudgetTotal,

    'Projected WO'[WO_Status] IN { "Initiated", "initiated" }

)

VAR OpenAmount =

CALCULATE (

    BudgetTotal,

    'Projected WO'[WO_Status] IN { "open", "Open" }

)

VAR AdjustedProjectedAmount = MAX( (ProjectedAmount - InitiatedAmount - OpenAmount ), 0)

RETURN

IF (

    NOT ( WOstatus IN { "Projected", "projected" } ),

    BudgetTotal,

    AdjustedProjectedAmount)
4

Did not quite understand. Can you send your PIX?

5

Here you go
The Problem.pbix (29.8 KB)

6

See if it helps
image

Revised Budget = 
VAR WOstatus =
    FIRSTNONBLANK ( 'Projected WO'[WO Status], 1 )
VAR ProjectedAmount =
    CALCULATE (
        [Original Budget],
        'Projected WO'[WO Status] IN { "Projected", "projected" }
    )
VAR InitiatedAmount =
    CALCULATE (
        [Original Budget],
        'Projected WO'[WO Status] IN { "Initiated", "initiated" }
    )
VAR OpenAmount =
    CALCULATE ( [Original Budget], 'Projected WO'[WO Status] IN { "open", "Open" } )
VAR AdjustedProjectedAmount =
    MAX ( ProjectedAmount - InitiatedAmount - OpenAmount, 0 )
RETURN
    SWITCH (
        TRUE (),
        NOT HASONEVALUE ( 'Projected WO'[WO Status] ), AdjustedProjectedAmount,
        NOT WOstatus IN { "Projected", "projected" }, [Original Budget],
        AdjustedProjectedAmount
    )
7

Hi,

Thanks for trying to help.

The new grand total should be 20, 935,403 I.e. what you get when you sum up what is in the Revised column not 0

8

Try again
image

Revised Budget = 
SUMX (
    VALUES ( 'Projected WO'[WO Status] ),
    CALCULATE (
        VAR WOstatus =
            FIRSTNONBLANK ( 'Projected WO'[WO Status], 1 )
        VAR ProjectedAmount =
            CALCULATE (
                [Original Budget],
                'Projected WO'[WO Status] IN { "Projected", "projected" }
            )
        VAR InitiatedAmount =
            CALCULATE (
                [Original Budget],
                'Projected WO'[WO Status] IN { "Initiated", "initiated" }
            )
        VAR OpenAmount =
            CALCULATE ( [Original Budget], 'Projected WO'[WO Status] IN { "open", "Open" } )
        VAR AdjustedProjectedAmount =
            MAX ( ProjectedAmount - InitiatedAmount - OpenAmount, 0 )
        RETURN
            SWITCH (
                TRUE (),
                NOT WOstatus IN { "Projected", "projected" }, [Original Budget],
                AdjustedProjectedAmount
            )
    )
)