Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
i have a fact table which contains many repetitions of a Supply Number with different usage values by date. I have a dim table that contains same supply number and various prices. I want to create a measure that multiplies usage by RElated price.
I can set out a table that lists supply number from fact table and correctly lists the various price elements from theDim table.
however when I try to write a measure
cost = sum(fact[usage])* RELATED(dim[price] it doesn't work. I am advised that the related table can't be found.
any advice gratefully received
Denis
Solved! Go to Solution.
I would iterate over the values of the dim[price] column or the dim table using one of these two measures:
cost = SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )
or
cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )
The red part can be replaced with a measure if you have already defined it.
In this scenario, since the multiply need to be done on each row level, you need to multiple the RELATED price in SUMX() function.
I assume you have table like below:
You just need to use SUMX() function.
Total Cost = SUMX('Fact','Fact'[Usage]*RELATED(Supply[Price]))
Regards,
I would iterate over the values of the dim[price] column or the dim table using one of these two measures:
cost = SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )
or
cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )
The red part can be replaced with a measure if you have already defined it.
Yes, of course. that's why the table function perfectly. They're related on Supply Number
de ks
In this scenario, since the multiply need to be done on each row level, you need to multiple the RELATED price in SUMX() function.
I assume you have table like below:
You just need to use SUMX() function.
Total Cost = SUMX('Fact','Fact'[Usage]*RELATED(Supply[Price]))
Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |