Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to obtain symilar effect for table like in case of dynamic measure selector for charts. So far I was able to obtain the view inserted below, however I need to find a way to add Actual data for past months to Forecast as well (without making a copy of Actuals). And I am strugling with filter/row context. Anyone can help, please?
Regards,
Karo
Solved! Go to Solution.
Ok. Here is one way.
with a Type table which is disconnected from other tables in the model:
Create new measure as follows (I'm referencing the measures posted in my previous reply):
Filtered measures =
SWITCH (
TRUE (),
MAX ( 'Type Selection'[Type] ) = "Sales", [Sum Sales],
MAX ( 'Type Selection'[Type] ) = "Target", [Sum Target],
[Combined Sales and Forecast]
)
Add the "Type" column from the Type selection table to the row bucket of a matrix, the year and month columns from the calendar table as columns, and the [Filtered measures] measure to the "Values" bucket.
and you will get the following:
Proud to be a Super User!
Paul on Linkedin.
Hi, @karo
You’re welcome.
If you have solved problem, please mark the solution to close this thread.
If you haven't, please feel free to ask.
Best Regards,
Community Support Team _ Eason
Hi, @karo
Change your measure [All] as below:
All2 =
VAR A =
IF (
ISBLANK ( [Forecast] ),
VAR tab =
SUMMARIZE (FILTER( ALL ( Actuals ), [Date]>=DATE(2020,09,01)),Actuals[Date], [Value] )
RETURN
SUMX (
FILTER (
tab,
YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[Year] )
&& MONTH ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
),
[Value]
),
[Forecast]
)
RETURN
IF (
HASONEVALUE ( 'Table'[Type] ),
SWITCH (
SELECTEDVALUE ( 'Table'[Type] ),
"Actuals", [Actuals],
"Budget", [Budget],
"Forecast", A
),
[Actuals] + [Budget] + A
)
If it doesn't meet your requirement,please feel free to let me know.
Best Regards,
Community Support Team _ Eason
@v-easonf-msft thank you so much! I will try to replicate your DAX in my report based on "real" data and in case of doubts I will go back
Hi, @karo
Measure [all] is composed of three other measures.
Is it convenient to show its code?
Best Regards,
Community Support Team _ Eason
Hi @v-easonf-msft , Yes and sure. I have prepared dummy .pbix based on Power BI Sample Data. In my real scenario Budget,Actuals and Forecast data are comming from different systems. What is the best way to share the pbix file ?
Regards,
Karo
Hi, @karo
For data security considerations, it is recommended that you upload the file to Onedrive for Bussiness and share the link.
Best Regards,
Community Support Team _ Eason
@v-easonf-msft I have shared the link with you via private message. Thanks a lot for looking into my case!
You need to create a measure to include actuals if the filter context if before the current month, or the forecast value if it is after.
Something along the lines of:
Combined =
VAR YM = YEAR(MAX('Calendar '[Date])) * 100 + MONTH(MAX('Calendar '[Date]))
VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY())
RETURN
SUMX(Calendar, IF(YM < CurrentYM, [your actuals measure], [your forecast measure])
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown , thank you for reply, however it is not solving my issue. Still context (please refer to 'Table'[Type] from my screens) is an issue.
Regards,
Karo
Is there a reason you need the slicer for type on this visual?
You can create a matrix with the values on rows like this:
Using the option under "Values" the Format pane and:
The measures I used to create the Measure to show sales or forecast:
1) The if measure to select sales or forecast:
calc sales or forecast =
VAR YM = YEAR(MAX('Calendar Table'[Date])) * 100 + MONTH(MAX('Calendar Table'[Date]))
VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY())
RETURN
IF(YM < CurrentYM, [Sum Sales], [Sum Forecast])
2) The final measure:
Combined Sales and Forecast =
SUMX (
SUMMARIZE (
'Calendar Table',
'Calendar Table'[YearMonth],
"Combined", [calc sales or forecast]
),
[Combined]
)
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown Yes, there is 🙂 I am trying to obtain similar effect for table like in case of dynamic measure selector for charts (please refer to https://visualbi.com/blogs/microsoft/powerbi/dynamic-measure-selection-power-bi/ as an example) .
Regards,
Karo
Ok. Here is one way.
with a Type table which is disconnected from other tables in the model:
Create new measure as follows (I'm referencing the measures posted in my previous reply):
Filtered measures =
SWITCH (
TRUE (),
MAX ( 'Type Selection'[Type] ) = "Sales", [Sum Sales],
MAX ( 'Type Selection'[Type] ) = "Target", [Sum Target],
[Combined Sales and Forecast]
)
Add the "Type" column from the Type selection table to the row bucket of a matrix, the year and month columns from the calendar table as columns, and the [Filtered measures] measure to the "Values" bucket.
and you will get the following:
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown Big, big thank you! I replicated your steps and it seems that it can be a solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |