当前位置: 动力学知识库 > 问答 > 编程问答 >

powerbi - Power BI: How to make a measure grouped off of another column

问题描述:

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!

分享给朋友:
您可能感兴趣的文章:
随机阅读: