Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |