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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nicoenz
Helper II
Helper II

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 II
Helper II

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 II
Helper II

Thanks!!!! very much appreciated

 

nicoenz
Helper II
Helper II

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 II
Helper II

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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