Forum Discussion
Summarize results
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.
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.
4 Replies
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]))
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.
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
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.
