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
10500438
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
foomanschu13
Advocate I
Advocate I

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.

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

Anonymous
Not applicable

"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

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.

lbendlin
Super User
Super User

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)

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.

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.

Top Solution Authors