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
j_rasmussen
Frequent Visitor

How to Multiply a calculated measure with a sum from another table, when not related directly

I have created 2 measures in Power BI that is based on different facttables.

Is it possible to calculate to multiply the 2 measures?

The tables are not directly linked and I have tried to multiply them using SumX with the reporting period as a filter but I am not able to get any result no matter if I include TestPrice in TestPrice4 or if I try to calculate them in a third measure. (Be aware, that DimCalcDBAll is a fact table in spite of the name)

​TestPrice = CALCULATE(IF ([FAT_QUANTITY]<=0;BLANK();SUMX(DimCalcDBAll;[FAT_QUANTITY]*1)))

TestPrice4 = SUMX( CALCULATETABLE('FactTPM_COPA';'DimCalendar'[ReportingPeriod]="2017.10"); [Volume])

Best regards

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @j_rasmussen,

 

Based on your formula, 'TestPrice4' is a measure who return fixed value from FactTPM_COPA table, it can be used as parameter; 'TestPrice' measure will genearte the dynamic result based on current records.

 

In summary, if you use them on visuals who exist columns which related to 'TestPrice' used, it is possible to get multiple result.

If drag to visuals who created by TestPrice4 related columns, they will only return the fixed value.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

I have tried to do as you suggest, but the calculated measure of the multiplication is totally blank. I tried to make a very simple measure, that does not include any filters or anything [TestPrice]*[TestPrice4], which actually gives me a result. But I expect that when I have to add filters then I will run into problems again.

 

So basically I think I need to calculate row by row using SUMX but here the first argument is TABLE and since I need to multiply results from 2 different tables, I supposed that generated my problem.

 

If you do not believe that is my issue, then please let me know.

 

regards

Jørgen

Hi @j_rasmussen,


Can you please share a sample pbix file to test? I not very clarity for the relationship between each tables and its date table.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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