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.
Example tables:
TABLE: 2019 Sale Data
Essentially I want to measure the annualized impact of the most recent cost change. I've calculated the most recent cost change by adding a column using rankx to rank the date (1 being the most recent) as the monthly cost file is added every month this will capture the latest:
Solved! Go to Solution.
Create calculate columns using dax as below:
Cost 1 Change =
IF (
'Cost Elements'[Date Rank] = 1,
CALCULATE (
SUM ( 'Cost Elements'[Cost 1] ),
FILTER (
ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
'Cost Elements'[Date Rank] = 1
)
)
- CALCULATE (
SUM ( 'Cost Elements'[Cost 1] ),
FILTER (
ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
'Cost Elements'[Date Rank] = 2
)
)
)
Table 2019 Sales Data =
IF (
'Cost Elements'[Date Rank] = 1,
VAR Item_ID = 'Cost Elements'[Item ID]
RETURN
CALCULATE (
MAX ( '2019 Sale Data'[2019 Qty Sold] ),
FILTER ( '2019 Sale Data', '2019 Sale Data'[Item ID] = Item_ID )
)
)
Cost 1 Change * 2019 Qty Sold = 'Cost Elements'[Cost 1 Change] * 'Cost Elements'[Table 2019 Sales Data]
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This worked great... i just did a sumx then of the cost change * qty sold and it worked great. Pulling the qty sold in over as a variable and the allexcept function is where I was falling short. Thanks!!!
Create calculate columns using dax as below:
Cost 1 Change =
IF (
'Cost Elements'[Date Rank] = 1,
CALCULATE (
SUM ( 'Cost Elements'[Cost 1] ),
FILTER (
ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
'Cost Elements'[Date Rank] = 1
)
)
- CALCULATE (
SUM ( 'Cost Elements'[Cost 1] ),
FILTER (
ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
'Cost Elements'[Date Rank] = 2
)
)
)
Table 2019 Sales Data =
IF (
'Cost Elements'[Date Rank] = 1,
VAR Item_ID = 'Cost Elements'[Item ID]
RETURN
CALCULATE (
MAX ( '2019 Sale Data'[2019 Qty Sold] ),
FILTER ( '2019 Sale Data', '2019 Sale Data'[Item ID] = Item_ID )
)
)
Cost 1 Change * 2019 Qty Sold = 'Cost Elements'[Cost 1 Change] * 'Cost Elements'[Table 2019 Sales Data]
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You should group this data bu common time dimension and should be able to do this
sumx(summarize(Item[Item ID],"_Cost",[Cost 1 Change], "_sum",sum('2019 Sales Data'[2019 Qty Sold])),[_Cost]*[_sum])
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |