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.
Hello, I want to select certain cost codes in the cost code column to allow divison by another value. In a very simplified version I want to total the cost of the A codes and divide by the average area of A's. I need the formula to write this.
Cost Code | COST | Area (m2) |
A.1 | £1 | 10 |
A.2 | £2 | 10 |
A.3 | £3 | 10 |
A.4 | £4 | 10 |
B.1 | £5 | 20 |
B.2 | £6 | 20 |
B.3 | £7 | 20 |
Hi, @hayali01 , why not make a small transformation to the dataset, which saves a lot of trouble and brings about much clarity in authoring measures.
Avg Cost =
DIVIDE (
CALCULATE (
SUM ( 'Table'[COST] ),
FILTER ( ALL ( 'Table' ), 'Table'[Region] = MAX ( 'Table'[Region] ) )
),
CALCULATE (
AVERAGE ( 'Table'[Area (m2)] ),
FILTER ( ALL ( 'Table' ), 'Table'[Region] = MAX ( 'Table'[Region] ) )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@hayali01 , Create a measure like
calculate(divide(sum(table[COST]), averageX(Table,table[Area (m2)])), filter( Table, left(Table[Cost Code],1) ="A"))
=VAR _c=LEFT(MAX('Table'[Cost Code]),FIND(".",MAX('Table'[Cost Code]),,99)-1) RETURN CALCULATE(DIVIDE(SUM('Table'[Cost]),AVERAGE('Table'[Area (m2)]),0),LEFT('Table'[Cost Code],FIND(".",'Table'[Cost Code],,99)-1)=_c)
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 |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |