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

[j_rasmussen]

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

[v-shex-msft]

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

[j_rasmussen]

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

[v-shex-msft]

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