Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nicoenz
Helper III
Helper III

Add a "fixed" amount to a stacked chart

Hi!!

I have a stacked chart:

nicoenz_0-1669812068300.png

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

2 ACCEPTED SOLUTIONS
nicoenz
Helper III
Helper III

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

View solution in original post

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] )
            )
        )
    )

yingyinr_0-1669975757703.png

Best Regards

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

View solution in original post

6 REPLIES 6
nicoenz
Helper III
Helper III

Thanks!!!! very much appreciated

 

nicoenz
Helper III
Helper III

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

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] )
            )
        )
    )

yingyinr_0-1669975757703.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nicoenz
Helper III
Helper III

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):

nicoenz_1-1669885994461.png

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

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

yingyinr_1-1669947365717.png

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)

yingyinr_0-1669947304806.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.