cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peter_Yaacoub
Frequent Visitor

Values show as Sum and Disappear from Table

Hello everyone!

 

I have a P&L in Power BI that looks like the below picture where all values are in YTD.

 

Two issues I am currently facing:

When choosing a specific month from the date slicer (i.e. February 2022), my “Last Year” values (February 2021) disappear. Is there a way to fix this and keep showing the parallel period values?

 

Also, when choosing more than one month in the slicer, my values are aggregated (i.e. February Net Profit Ratio shows as the sum of Jan and Feb NP Ratios). Any way to reflect the true ratios without aggregation?

 

Your help is greatly appreciated!

 

Power BI TablePower BI Table

My desired output is the following table:

 

Peter_Yaacoub_3-1654609070825.jpeg

 

Thanks in advance,

 

Peter

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Peter_Yaacoub,

#1, This should relate to the filter effect, the filter effect will also apply to your table based on the relationship key to limit the calculation ranges. For this scenario, you can create an unconnected table as a source of the slicer and write DAX expression to extract the selection and use it as conditions to calculate with your formula.

#2, The aggregation feature is the basic part of visual usage, you can't prevent them on visual effects. If you want to show the specific records, you can do an aggregate(e.g. max, min) on your selection results to only get one value.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Peter_Yaacoub,

#1, This should relate to the filter effect, the filter effect will also apply to your table based on the relationship key to limit the calculation ranges. For this scenario, you can create an unconnected table as a source of the slicer and write DAX expression to extract the selection and use it as conditions to calculate with your formula.

#2, The aggregation feature is the basic part of visual usage, you can't prevent them on visual effects. If you want to show the specific records, you can do an aggregate(e.g. max, min) on your selection results to only get one value.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Peter_Yaacoub , how are you creating last year ytd?

 

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

or

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi Amit,

 

Thank you for your response.

 

My values are in YTD and I calculated my last year Sales as follows:

 

Last Year Net Sales = CALCULATE(SUM(UK_Profit_and_Loss[Net Sales]),UK_Profit_and_Loss[Scenario]="Last Year").
 
The issue is that values are showing as aggregates if choosing any month other than January. I have provided a screenshot of my current model in case I am missing something there.
 
Thanks for the help and glad to have given a sub on YT.
 
Best,
Peter
 
Peter_Yaacoub_1-1654615372847.png

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors