问题描述:

I am just starting out with Power BI and having the most frustrating time trying to figure out this issue.

Here is a very small example of the data I pull in from an Excel file. Cannot get it any other way.

`╔══════════════╦══════════════════╦═══════════════╦═══════════════════════╗`

`║ Product Type ║ Product Name ║ Dollar Sales ║ Product Share of Type ║`

`╠══════════════╬══════════════════╬═══════════════╬═══════════════════════╣`

`║ Bagels ║ Client 1 Bagels ║ $15,521.09 ║ 2.42 ║`

`║ Bagels ║ Client 2 Bagels ║ $112,059.11 ║ 17.46 ║`

`║ Muffins ║ Client 1 Muffins ║ $141,417.84 ║ 3.25 ║`

`║ Muffins ║ Client 2 Muffins ║ $1,483,643.52 ║ 34.11 ║`

`║ Muffins ║ Client 3 Muffins ║ $19,008.64 ║ 0.44 ║`

`║ Pies ║ Client 2 Pies ║ $1,718,242.24 ║ 43.90 ║`

`║ Pies ║ Client 3 Pies ║ $39,883.20 ║ 1.02 ║`

`║ Pies ║ Client 4 Pies ║ $282,140.64 ║ 7.21 ║`

`╚══════════════╩══════════════════╩═══════════════╩═══════════════════════╝`

I do not have all Bagel products, just a select few that I do work with. Same for the other products in this data set.

So if I want to calculate what the total dollar sales of all Bagel Type products are, I can get pretty close with what I have available.

I can calculate the total Dollar sales of Type by making a calculated column of `Dollar Sales/(Product Share of Type/100)`

. Example Results:

`╔══════════════╦══════════════════╦════════════════════╗`

`║ Product Type ║ Product Name ║ Type Total Dollars ║`

`╠══════════════╬══════════════════╬════════════════════╣`

`║ Bagels ║ Client 1 Bagels ║ $641,367.36 ║`

`║ Bagels ║ Client 2 Bagels ║ $641,804.75 ║`

`║ Muffins ║ Client 1 Muffins ║ $4,351,318.15 ║`

`║ Muffins ║ Client 2 Muffins ║ $4,349,585.22 ║`

`║ Muffins ║ Client 3 Muffins ║ $4,320,145.45 ║`

`║ Pies ║ Client 2 Pies ║ $3,913,991.44 ║`

`║ Pies ║ Client 3 Pies ║ $3,910,117.65 ║`

`║ Pies ║ Client 4 Pies ║ $3,913,185.02 ║`

`╚══════════════╩══════════════════╩════════════════════╝`

In bagels, we get:

- $641,367.36 Total Type Dollars for
`Client 1 Bagels`

- $641,804.75 Total Type Dollars for
`Client 2 Bagels`

- This gives a very small difference of $437.39.

The small differences don't really worry me.

The problem is the result is calculated for each row (because I used the calculated column, and I don't know another way). If I turn around and want to get a total of all Type dollars, then the values are getting counted for each record. This will double, triple, quadruple, etc. based off of how ever many product items I have in my data set.

In the case of bagels:

- $1,283,172.11 Total Type Dollars
- This is basically a double count

So how can I create a measure that takes into account another column?

If I were to sum(Dollar Sales) for Product Type, and sum(Product Share of Type) for Product Type, it would be that much more accurate, and I wouldn't be double counting the finished value.

(I don't need the table as an output, I just need a measure that would give me the same results as the final column if I were to create a matrix piece with Power PI):

`╔══════════════╦═══════════════════╦════════════════════╦════════════════════╗`

`║ Product Type ║ Sum(Dollar Sales) ║ Sum(Share of Type) ║ Type Total Dollars ║`

`╠══════════════╬═══════════════════╬════════════════════╬════════════════════╣`

`║ Bagels ║ $127,580.20 ║ 19.88 ║ $641,751.51 ║`

`║ Muffins ║ $1,644,070.00 ║ 37.80 ║ $4,349,391.53 ║`

`║ Pies ║ $2,040,266.08 ║ 52.13 ║ $3,990,535.35 ║`

`╚══════════════╩═══════════════════╩════════════════════╩════════════════════╝`

I have tried `CALCULATE()`

and `SUMX()`

but I still cannot seem to get subtotals based off of product type before making the final calculation.

I did try creating a `GROUPBY()`

table of values, but then the problem is I cannot have those results in the same table/graph as the other results because filters don't apply (I'm running basically everything from this one data set/table).

Any thoughts?

Thanks!

Try this:

```
TotalSales := CALCULATE(SUM(TableName[Dollar Sales]),
All(TableName[Product Name]
)
```

What you're doing with that formula is ignoring any filters imposed by the PivotTable on the *Product Name* column, therefore it will give you the total for any other field that you place in the Pivot. Therefore, if you create a PivotTable with *Product Type* and *Product Name* in the row labels, it will give you the total by Product Type.

Hope this helps!