Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
i have a issue about my dax measure.
Solved! Go to Solution.
try this
Measure = VAR __Summary = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Calendar'[YearMonth] ), "Quantity CY", CALCULATE ( SUM ( 'Table'[Quantity] ) ), "Avg Cost LY", [Average Cost LY] ) RETURN SUMX ( __Summary, [Quantity CY] * [Avg Cost LY] )
the total is alignes with your logic here
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
Column1 | Column2 |
A | 1 |
B | 2.5 |
Hi,
thanks overall
Date | Quantity | Cost | Coste Total |
01/2018 | 5 | 10 | 50 |
01/2018 | 5 | 10 | 50 |
01/2018 | 3 | 20 | 60 |
01/2018 | 3 | 5 | 15 |
01/2018 | 5 | 5 | 25 |
01/2018 | 5 | 3 | 15 |
01/2018 | 5 | 4 | 20 |
01/2018 | 5 | 4 | 20 |
01/2018 | 5 | 5 | 25 |
01/2019 | 5 | 5 | 25 |
01/2019 | 56 | 0,5 | 28 |
01/2019 | 56 | 0,5 | 28 |
01/2019 | 5 | 10 | 50 |
01/2019 | 5 | 10 | 50 |
01/2019 | 3 | 20 | 60 |
01/2019 | 3 | 5 | 15 |
01/2019 | 5 | 5 | 25 |
01/2019 | 5 | 3 | 15 |
01/2019 | 5 | 4 | 20 |
01/2019 | 5 | 4 | 20 |
01/2019 | 5 | 5 | 25 |
01/2019 | 5 | 5 | 25 |
01/2019 | 56 | 0,5 | 28 |
02/2019 | 56 | 0,5 | 28 |
02/2019 | 5 | 0,5 | 2,5 |
02/2019 | 5 | 0,5 | 2,5 |
02/2019 | 5 | 10 | 50 |
02/2019 | 5 | 10 | 50 |
02/2019 | 3 | 20 | 60 |
02/2019 | 3 | 5 | 15 |
02/2019 | 5 | 5 | 25 |
02/2019 | 5 | 3 | 15 |
02/2019 | 5 | 4 | 20 |
02/2019 | 5 | 4 | 20 |
02/2019 | 5 | 5 | 25 |
02/2019 | 5 | 5 | 25 |
02/2019 | 56 | 0,5 | 28 |
02/2019 | 56 | 0,5 | 28 |
02/2019 | 5 | 0,5 | 2,5 |
02/2019 | 5 | 0,5 | 2,5 |
Then i need to calculate a 1 measure Average of Cost LY (01/2018)
Then i need to multiplie Quantity this year (01/2019) by Average of Cost LY (01/2018) an sum. This sum is error.
THanks
try this
Measure = VAR __Summary = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Calendar'[YearMonth] ), "Quantity CY", CALCULATE ( SUM ( 'Table'[Quantity] ) ), "Avg Cost LY", [Average Cost LY] ) RETURN SUMX ( __Summary, [Quantity CY] * [Avg Cost LY] )
the total is alignes with your logic here
Thanks, this solution is perfect.
you need to use SUMX here, e.g. with SUMMARIZECOLUMNS (with whatever is in the rows before the numbers you show)
right now PowerBI adds all rows and then multiplies
you want it to multiply and then add rows, SUMX can do that
Thanks, but I can't think of a way to do the truth.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |