cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Percentage of a grand total for a measure in another calculation

Hi the community,

I've tried to dig into the community topics for a couple of days but unfortunately didn't find what I need. Probably I formulate this question incorrect but it seems pretty straightforward.

So, I have a calculated measure that represents the average value of some data by two categories by month. Let's say it looks like this in Excel:

Note that this matrix represent average value by selected months for filtered rows/column categories, and all the values are transformed to percentages of grand total.

To represent these results I'm using

Measure1=DIVIDE(CALCULATE(SUM(Table[Sales], FILTER(Table[Sales], Table[Category] in (A, B, C) && Table[Region] in (X, Y, Z))), DISTINCTCOUNT(Table[Month Year]))
Measure2=[Measure1] / CALCULATE([Measure1], ALLSELECTED[Table])

And the rows are Category and columns are Region

I also have another table with some data (again, Pivots in Excel). Again, this is another filtered matrix with some other rules, but only the first calculated column is interested for us.

To create this table I'm using the following measure:

Measure3 = DIVIDE(CALCULATE(SUM(Table[Sales]), FILTER(Table, Table[Category] IN (A, D, E) && Table[Region] in (M, N, O))), DISTINCTCOUNT(Table[Month Year]))

The same: Rows are Category, Columns are Regions.

So what I need is to create a measure that will take each percentage of a grand total from the first matrix (28.06; 0.03 etc) and multiply it to the orange values in the second table (73.24; 29,51 etc), so I will have a table

 table1column2 table1column3 table1column4 table2row4 73.24*0.2806 73.24*0.0003 73.24*0.1077 table2row6 29,51*0.2806 29,51*0.0003 29,51*0.1077

I've tried to make SUMX but can't find a way to use it there - as it tries to find me the matching values from the second table (or probably I'm doing wrong)

TIA!!

3 REPLIES 3
Frequent Visitor

Sorry for the late reply, I have a sample of the data. Based on the total sales in different countries I want to make projections on sales on new countries for the specific categories as it's shown in the table.

(sorry I don't understand how to insert a file here)

Don't ask about the meaningless of the data - in reality it's something completely different and that's why I need to make projections, but the structure is the same.

Forgot to mention that this data is only for one month, but in reality it's 12 month data and first we calculate the average of selected months - like May 2024, January 2024 and August 2023 (Idon't know how it is done in Excel)

Community Support

Hi, @krtek

Without seeing the exact structure of the tables and the relationships between them, I can provide a conceptual example:

Measure4 =
SUMX(
VALUES(Table[Category]),
[Measure1] * LOOKUPVALUE([Measure3], Table[Category], EARLIER(Table[Category]), Table[Region], EARLIER(Table[Region]))
)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi @v-yohua-msft! Thanks for the reply.

I didn't have time to prepare the sample data. The formula you've provided unfortunately doesn't work as measures can't be used in the LOOKUPVALUE (not RELATED or anything like this)

So we have two measures: both of them have sales by region and category, but the categories and regions are different. The first one is calculated by Regions 1, 5, 6 and by categories A - G, and the raw sales data is used The second one is calculated by Regions 2 - 5 and by Categoiries A - K, and the percentage of a grand total is applied for each value.

Both measures represent average by month (SUM/DISTINCTCOUNT(Months)).

Therefore the following measures are done:

- The first measure is filtered by categories A, B, C, D and the region 1.
- The region total percent is used for the second measure for the regions 2 - 5.

What I need is to multiply each region total percent in the second measure by the value for the category A - D from the first measure, so the final table (where rows are categories and columsn are regions) will look like

CategoryA*Region2 CategoryA*Region3

CategoryB*Region2 CategoryB*Region3

In Excel this can be represented by the formula for the ROW Category A and COLUMN Region 2

= GETPIVOTDATA("Mean Sales by Month";Calc_Tables!\$B\$57;"REGIONS";"Region 1";"CATEGORIES";"Category A")
* GETPIVOTDATA("Mean Sales by Month";Calc_Tables!\$B\$24;"REGIONS";"Region 2")

I hope it helps.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors