cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Sum based on selection

I wasn't sure exactly how to title this question.

 

I have a table that has Four columns, Type, Version, Month and quantity. In January, all twelve months are loaded and each month type is Forecast, Version is V1. Then in February, I add twelve more rows, January is Actuals, February through December is Forecast, Version is V2. Then in March, I add twelve more rows, January, February type is Actuals and March through December is Forecast, Version is V3.

10500438_1-1593700264963.png

 

I have a slicer for Version. When the user selects V3, I need it to sum January, February, Actuals and March through December Forecast. If I create the table as I have it shown above then it is pretty straight forward, but my data model is large (there are 300k rows for each month). 

 

What I would like to accomplish is: I load the table with the V1, 12 months forecast, then in February I load January Actuals, and February through December Forecast in V2 (because the forecast amounts change each time a version is created). Then in March, I load February actuals, March through Decembert forecast into V3. 

 

When the user selects V2, the formula should grab January Actuals, February through December V2 forecast. If the user selects V3, the formula grabs January and February actuals, March through December V3 Forecast.

 

Thoughts on the best way to accomplish this? I thought of a switch formula (Metric Selected) or maybe a index table that has all the various scenarios listed. But wanted to see the thoughts of others.

 

 

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Sum based on selection

Does the type actually matter?  Can't you just put the version numbers in the columns of a matrix visual and the month in the rows?  (with sorting column of course)

Highlighted
Helper II
Helper II

Re: Sum based on selection

Well the type matters because based on the type, i have calculations that use this field to determine how to calculate. But it does not matter in the visual matrix, I only included it to make it easier for people reading this to see what I was trying to explain.

 

I got it working but I didn't know if there was an easier way to accomplish it.

Highlighted
Solution Sage
Solution Sage

Re: Sum based on selection

"there are 300k rows for each month"

How is this possible? How many years do you keep in the table? And, if there are many years, you should also have a column somewhere that tells you which year of the month it is.... RIGHT?

Best
D
Highlighted
Helper II
Helper II

Re: Sum based on selection

Yes, I have a year column, there are currently three years in the model. Each sales order item has individual products, all with various characteristics. I use these characteristics for different visuals and the users like to see multiple years at one time.

 

I try to use excel to reduce the data set to a more manageable data set but it still ends up being a lot of data.

Highlighted
Advocate I
Advocate I

Re: Sum based on selection

Could you do something where you Calculate the Actuals and add in the selected forecast?

 

measure = CALCULATE(SUM(Quantity),Type="Actuals")+CALCULATE(SUM(Quantity),Type="Forecast",Version=SELECTEDVALUE(Version))

 

You may want to break up those into individual measures or use variables but the idea is there.

Highlighted
Helper II
Helper II

Re: Sum based on selection

Thank you, I will look into selected value, I have used it before and it is very handy. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors