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!
