Summarize results

[hitesh160790]

I have a data model with 3 tables

1) ForecastPlan

Product No	Forecast Plan
1	10
2	20

2) fact

Product No	Diet	Qty Needed (a)
1	Veg	20
2	Veg	30
3	N Veg	10

3) Product

Product NO	Name
1	A
2	B

Relationships:

Product to Forecast Plan (1 to many)

Product to fact (1 to many)

I am getting below results (which is correct)

Product No	Diet	Qty Needed (a)	Forecast Plan (b)	Component	Final (a*b)
1	Veg	20	10	C1	200
2	Veg	30	20	C1	600
3	N Veg	10	10	C2	100

But I need summarized results

Veg : Final = 800

N Veg : Final = 100

I assume it is doing (20+30)*(10+20).

Can anyone help me out.

[AllisonKennedy]

Sorry, I'm not sure I understand what the question is? Are you able to use a matrix visualization for this? 

Try using ALLEXCEPT(Fact[Diet]) in your measure if you still need the qty and other info in the table too:

Final Diet = CALCULATE([final], ALLEXCEPT(Fact[Diet]))

[hitesh160790]

If I use matrix I am getting

Product No	Qty Needed (a)	Forecast Plan (b)	Component	Final (a*b)
Veg (Diet) 1 (Product No) 2 (Product No)	20 30	10 20	C1 C1	200 600
TOTAL	50	30		1500

I need

Product No	Qty Needed (a)	Forecast Plan (b)	Component	Final (a*b)
Veg (Diet) 1 (Product No) 2 (Product No)	20 30	10 20	C1 C1	200 600
TOTAL		No Total Here		800

[v-xuding-msft · Accepted solution]

Hi @hitesh160790 ,

I have created a sample. I'm not clear how you want to show. 

If you want to show in table visual,  please try this:

Measure = SUM('Fact'[Qty Needed (a)])* SUM(ForecastPlan[Forecast Plan])

Measure 2 = SUMX(ALLEXCEPT('Fact','Fact'[Diet]),[Measure])

If you do it in matrix visual, please try:

Measure 3 = SUMX('Fact',[Measure])

For more details, please see the attachment.

[hitesh160790]

Hi @AllisonKennedy 

I tried using AllExcept.

I simply need the final result which is 

Veg : Final = 800

N Veg : Final = 100

My model is doing (20+30)*(10+20) which gives me 1500.