Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm new to DAX measurements and I'm having trouble solving a problem. I need to get the average prices of the products. However, the calculation should consider only products that have data in all selected years. For example, code A has data in 2018, 2019 and 2020, code B in 2018 and 2019, in which case the average must be only for code A. If only the years 2018 and 2019 are segmented, then code B can be calculated . Sounds simple, but with my knowledge I'm not able to find a formula that does this. Here is an example of the table:
Solved! Go to Solution.
Hi @Josué ,
Maybe you can try this code to do that:
Measure =
VAR _AllYear0fCode =
CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table'[year] ) )
VAR _SelectedYear =
CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table' ) )
VAR _Compare =
COUNTROWS ( EXCEPT ( _SelectedYear, _AllYear0fCode ) )
VAR _AVG =
AVERAGEX ( VALUES ( 'Table'[year] ), SUM ( 'Table'[sales] ) )
RETURN
IF ( _Compare = 0, _AVG )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Josué ,
Maybe you can try this code to do that:
Measure =
VAR _AllYear0fCode =
CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table'[year] ) )
VAR _SelectedYear =
CALCULATETABLE ( VALUES ( 'Table'[year] ), ALLSELECTED ( 'Table' ) )
VAR _Compare =
COUNTROWS ( EXCEPT ( _SelectedYear, _AllYear0fCode ) )
VAR _AVG =
AVERAGEX ( VALUES ( 'Table'[year] ), SUM ( 'Table'[sales] ) )
RETURN
IF ( _Compare = 0, _AVG )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Josué , A measure like
AverageX(Summarize(Table, Table[Year], Table[Product], "_1", Sum(Table[Price])),[_1])
or
calculate(AverageX(Summarize(Table, Table[Year], Table[Product], "_1", Sum(Table[Price])),[_1]), allexcept(Table, Table[Product])
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |