Percentage of a grand total for a measure in another calculation

[krtek]

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!!

[krtek]

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)

https://docs.google.com/spreadsheets/d/19s69EK-ab8SrSpJSKo8gaJ6NObDKhlqj/edit?usp=sharing&ouid=11834...

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) 

[v-yohua-msft]

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)

How to Get Your Question Answered Quickly 

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.

[krtek]

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.