Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.