The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
36 | |
14 | |
12 | |
7 | |
7 |