The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi I've got regional results data that is showing Gross margin, volumes and margin values for each forecasting scenario (1+11 meaning we've got 1 month of actuals and 11 months forecasted, 2+10 meaning 2 months actual and 10 forecasted etc) for 2025 in a following structure
Segment | Year | Scenario | Location | Type | Month | Value | Date | Month No |
America | 2025 | 1+11 | Chile | GM | Jan | 100.00 | 1-Jan-25 | 1 |
America | 2025 | 1+11 | Chile | GM | Feb | 150.00 | 1-Feb-25 | 2 |
America | 2025 | 1+11 | Chile | GM | Mar | 170.00 | 1-Mar-25 | 3 |
America | 2025 | 1+11 | Chile | Margin | Jan | 2.00 | 1-Jan-25 | 1 |
America | 2025 | 1+11 | Chile | Margin | Feb | 1.88 | 1-Feb-25 | 2 |
America | 2025 | 1+11 | Chile | Margin | Mar | 2.83 | 1-Mar-25 | 3 |
America | 2025 | 1+11 | Chile | Volume | Jan | 50.00 | 1-Jan-25 | 1 |
America | 2025 | 1+11 | Chile | Volume | Feb | 80.00 | 1-Feb-25 | 2 |
America | 2025 | 1+11 | Chile | Volume | Mar | 60.00 | 1-Mar-25 | 3 |
America | 2025 | 2+10 | Chile | GM | Jan | 100.00 | 1-Jan-25 | 1 |
America | 2025 | 2+10 | Chile | GM | Feb | 120.00 | 1-Feb-25 | 2 |
America | 2025 | 2+10 | Chile | GM | Mar | 170.00 | 1-Mar-25 | 3 |
America | 2025 | 2+10 | Chile | Margin | Jan | 2.00 | 1-Jan-25 | 1 |
America | 2025 | 2+10 | Chile | Margin | Feb | 2.40 | 1-Feb-25 | 2 |
America | 2025 | 2+10 | Chile | Margin | Mar | 2.83 | 1-Mar-25 | 3 |
America | 2025 | 2+10 | Chile | Volume | Jan | 50.00 | 1-Jan-25 | 1 |
America | 2025 | 2+10 | Chile | Volume | Feb | 50.00 | 1-Feb-25 | 2 |
America | 2025 | 2+10 | Chile | Volume | Mar | 60.00 | 1-Mar-25 | 3 |
I want to create a matrix table that shows Segment > Location > Type in rows and in columns whole 2025 and actuals based on scenario slicer, - so if I select 2+10 in slicer it will show me only values from this scenario - for total year and for 2 months.
In order to do that I've created separate measures for each column - for Year Total and for Actuals:
Can you please help me with how should I build the divide part for actuals so that is works?
Solved! Go to Solution.
Hi @alarekawek please try this
Hi @alarekawek please try this
Thank you it works!!
@alarekawek , Try using
Actuals 2025 Corrected =
IF(
SELECTEDVALUE(Append1[Type]) = "Margin",
DIVIDE(
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]) &&
Append1[Type] = "GM"
)
),
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario]) &&
Append1[Type] = "Volume"
)
),
0
),
CALCULATE(
SUM(Append1[Value]),
FILTER(
Append1,
Append1[Month No] <= [Switch] &&
Append1[Year] = "2025" &&
Append1[Scenario] = SELECTEDVALUE(Sort[Scenario])
)
)
)
Proud to be a Super User! |
|
Hi, thank you for the answer, unfortunately this does not solve the problem 😞
I dividided the margin calculation formula into parts and it does work, but I just cannot get it to show in the margin line, it shows only in the Chile level
User | Count |
---|---|
26 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |