Skip to main content
cancel
Showing results for 
Search instead 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

Reply
krtek
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:

Screenshot 2024-04-10 at 14.34.20.png

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.

Screenshot 2024-04-10 at 14.38.12.png

 

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

 

 table1column2table1column3table1column4
table2row473.24*0.280673.24*0.000373.24*0.1077
table2row629,51*0.280629,51*0.000329,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
krtek
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.

Screenshot 2024-06-13 at 14.49.13.png

(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
Community Support
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)

 

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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