Add a "fixed" amount to a stacked chart

[nicoenz]

Hi!!

I have a stacked chart:

that accumulates sales per year (there's a legend the status of the pipeline where blue is "Actual" value).

EBITDA pipeline total =

CALCULATE(

    SUM('1. Dim_ANT_Impacts'[Value (m)]),
    FILTER(        ALLSELECTED(Datetable[Year]),         ISONORAFTER(Datetable[Year], MAX(Datetable[Year]), DESC)))

The program started in 2021 but started collecting the data in 2022.

Therefore i would like to add 2021's value (lets say 50M as Actual).

If I use the same measure and adding "+ 50" it will add the number to all categories, which is not correct.

Any idea on how to increase the total value by 50M as actual?

regards and thanks,

nico

[nicoenz · Accepted solution]

Hi @v-yiruan-msft ,

thanks it worked partly wellt!!! i lost the accumulated chart. your visual doesn't show it. If i combine all, then i loose the colour schemes

thanks

[nicoenz]

Thanks!!!! very much appreciated

[nicoenz · Accepted solution]

Hi @v-yiruan-msft ,

thanks it worked partly wellt!!! i lost the accumulated chart. your visual doesn't show it. If i combine all, then i loose the colour schemes

thanks

[v-yiruan-msft · Accepted solution]

Hi @nicoenz ,

Please update the formula of measure as below, and you can get a culmulative values... Please check the update in the attachment...

Mfeasure =
VAR _selpaf =
    SELECTEDVALUE ( 'PAF'[PAF] )
RETURN
    IF (
        _selpaf = " Actual 2021",
        500000,
        CALCULATE (
            SUM ( Sheet1[Value] ),
            FILTER (
                ALLSELECTED ( 'Sheet1' ),
                'Sheet1'[PAF] = _selpaf
                    && YEAR ( 'Sheet1'[Period] ) <= SELECTEDVALUE ( 'Datetable'[Year] )
            )
        )
    )

Best Regards

[nicoenz]

Hi @v-yiruan-msft 

Your proposal did added it as a 2021 value but i need also all the other ones to include that increase. The expected result should look something like this (when compared to the initial above chart):

I want to create an "artifical" block of for all years that increses all totals by 50M (representing the actuals of 2021)

I attached link to an extract of the model (with randomized values and no sensitive data)

Sample pbix model ,

Regards,

Nico

[v-yiruan-msft]

Hi @nicoenz ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Create a PAF dimension table

2. Create a measure as below

Measure = 
VAR _selpaf =
    SELECTEDVALUE ( 'PAF'[PAF] )
RETURN
    IF (
        _selpaf = " Actual 2021",
        500000,
        CALCULATE (
            SUM ( Sheet1[Value] ),
            FILTER (
                ALLSELECTED ( Datetable[Year] ),
                ISONORAFTER ( Datetable[Year], MAX ( Datetable[Year] ), DESC )
            ),
            FILTER ( 'Sheet1', 'Sheet1'[PAF] = _selpaf )
        )
    )

3. Update the visual field setting (Column y-axis: Measure  Column legend: PAF of PAF dimension table)

Best Regards

[v-yiruan-msft]

Hi @nicoenz ,

You can update the formula of measure [EBITDA pipeline total] as below and check if you can get the expected result...

EBITDA pipeline total =
IF (
    SELECTEDVALUE ( Datetable[Year] ) = 2021,
    50000000,
    CALCULATE (
        SUM ( '1. Dim_ANT_Impacts'[Value (m)] ),
        FILTER (
            ALLSELECTED ( Datetable[Year] ),
            ISONORAFTER ( Datetable[Year], MAX ( Datetable[Year] ), DESC )
        )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards