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.
I am currently facing a problem in finding a suitable DAX formula for resolving an issue.
ERP report provides information in the format below. It lists all GL first and then cost centre as a sub-total row.
GL Amount
9000 10000
9001 20000
1010 30000
9002 1000
9005 2000
1011 3000
I want to transform my table in the format below
GL Cost Centre Amount
9000 1010 10000
9001 1010 20000
1010 1010 30000
9002 1011 1000
9005 1011 2000
1011 1011 3000
Appreciate if anyone can help
Solved! Go to Solution.
@Anonymous
File attached as well
@Anonymous
Give this a shot.
First Add an Index Column from Query Editor
Then add this calculated column
Cost Centre = VAR GL_Character = 9 VAR Cost_Centre = 1 RETURN IF ( VALUE ( LEFT ( [GL] ) ) = GL_Character, MINX ( TOPN ( 1, FILTER ( Table1, [Index] > EARLIER ( [Index] ) && VALUE ( LEFT ( [GL] ) ) = Cost_Centre ), [Index], ASC ), [GL] ), [GL] )
@Anonymous
File attached as well
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |