Hello Every one,
I have created a power pivot report based on the following tables
Table No.1
<colgroup><col><col></colgroup><tbody>
</tbody>
My Power Pivot report looks like this
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
My query is the subtotal of Electronics group forecast sale column showing as zero value which wrong.
I have created measures for each column as follows
target column = sumifs(Table 2 sumrange)
Total Sold column = sumifs(Table 4 sumrange)
Forecast Sale column = if((sumifs(Table 3 sumrange)-sumifs(table 4 sumrange))<0,0,(sumifs(Table 3 sumrange)-sumifs(table 4 sumrange)))
Variance = Target column - Total Sold column - Forecast Sale column
The correct figure should reflect on group level in Forecast Sale column as 45000 and in Variance column as - 10000.
Hope i have explained my issue properly.
Thank you so much in advance.
Best Regards
I have created a power pivot report based on the following tables
Table No.1
Product Name | Group |
Laptop | Electronic |
Television | Electronic |
Washing Machines | Electronic |
Refrigerator | Electronic |
Mobile Phones | Electronic |
Table No.2 | |
Product Name | Target |
Laptop | 100,000.00 |
Television | 80,000.00 |
Washing Machines | 70,000.00 |
Refrigerator | 70,000.00 |
Mobile Phones | 150,000.00 |
Table No.3 | |
Product Name | Forecast Sale |
Laptop | 100,000.00 |
Television | 80,000.00 |
Washing Machines | 70,000.00 |
Refrigerator | 70,000.00 |
Mobile Phones | 150,000.00 |
Table No.4 | |
Product Name | Sold |
Laptop | 90,000.00 |
Television | 75,000.00 |
Washing Machines | 80,000.00 |
Refrigerator | 65,000.00 |
Mobile Phones | 125,000.00 |
<colgroup><col><col></colgroup><tbody>
</tbody>
My Power Pivot report looks like this
Target | Total Sold | Forecast Sale | Variance | |
Electronics (Sub-Total) | 470,000.00 | 435,000.00 | 0.00 | 35,000.00 |
Laptop | 100,000.00 | 90,000.00 | 10,000.00 | 0.00 |
Television | 80,000.00 | 75,000.00 | 5,000.00 | 0.00 |
Washing Machines | 70,000.00 | 80,000.00 | 0.00 | -10,000.00 |
Refrigerator | 70,000.00 | 65,000.00 | 5,000.00 | 0.00 |
Mobile Phones | 150,000.00 | 125,000.00 | 25,000.00 | 0.00 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
My query is the subtotal of Electronics group forecast sale column showing as zero value which wrong.
I have created measures for each column as follows
target column = sumifs(Table 2 sumrange)
Total Sold column = sumifs(Table 4 sumrange)
Forecast Sale column = if((sumifs(Table 3 sumrange)-sumifs(table 4 sumrange))<0,0,(sumifs(Table 3 sumrange)-sumifs(table 4 sumrange)))
Variance = Target column - Total Sold column - Forecast Sale column
The correct figure should reflect on group level in Forecast Sale column as 45000 and in Variance column as - 10000.
Hope i have explained my issue properly.
Thank you so much in advance.
Best Regards