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
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).
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
Solved! Go to 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
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
Thanks!!!! very much appreciated
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] )
)
)
)
Best Regards
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)
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |