How to create a measure that applies context and sums correctly in Power BI?

[apohl1]

Hi everyone,

I'm working with a table similar to the one below and facing a challenge in creating a measure for the last column ("Forecasted Q2"). The goal is to calculate this value based on the Product class for each product.

The main challenge is that the measure needs to:

• Take the context of the Product class into account when calculating each row.
• Still aggregate correctly in the total row, so the sum at the bottom reflects the sum of individual forecasted values, not just a simple total of the sales.

Could anyone suggest how to write such a measure in Power BI that respects the row context but also calculates the total sum accurately?

Thanks in advance!

Product code	Product class	Sales Q1	Forecast approach Q2	Forecasted Q2
Product A	Focus	100	Q1 + 10%	110
Product B	Focus	10	Q1 + 10%	11
Product C	Focus	30	Q1 + 10%	33
Product D	Not focus	50	Q1	50
Total		190		204

[v-ssriganesh]

Hello @apohl1,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @apohl1,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I’ve reproduced your scenario in Power BI Desktop based on the table structure and logic you shared and I was able to get the expected output, where:

• The forecast calculation is based on the Product class (e.g. “Focus” adds 10%)
• The total row correctly reflects the sum of individual forecasted values (not a bulk calculation)

I created:

• A SalesTable with your product, class, and Q1 sales values.
• A ForecastTable with each Product class and its corresponding forecast factor.
• A relationship between SalesTable[Product class] and ForecastTable[Product class] with: Cardinality: Many-to-One (*:1) and Direction: Single

Here’s the DAX measure I used to calculate Forecasted Q2:

Forecasted Q2 =

SUMX (

    SalesTable,

    SalesTable[Sales Q1] * RELATED(ForecastTable[Forecast Factor])

)

For your reference and testing, I’m attaching the .pbix file.

Best regards,
Ganesh Singamshetty.

[apohl1]

Thank you! Your solution would have worked if I was able to setup a relationship between the two tables. Unfortunately, my model is more complex and it's not possible. Howerver, I managed to get to the results I wanted with the following formula: 

Forecasted Q2 = CALCULATE(Salestable[Sales]*1.05,filter(Producttable,[Product class]="Focus"))+CALCULATE(Salestable[Sales],filter('Producttable,[Product class]="Non-Focus"))

[apohl1]

Thank you! Sorry I should have clarified that "Forecast factor" and "Sales" are stored in two different tables. Therefore, this solution doesn't work. 

[DataInsights]

@apohl1,

I created a column [Forecast factor Q2] with the amount to multiply by Sales Q1.

Measure:

Forecasted Q2 = 
SUMX ( Table1, Table1[Sales Q1] * Table1[Forecast factor Q2] )