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.
Hi Legends,
I got answered by @v-jingzhang (Many thanks) on a previous post how to get the total amount of a project but now I am struggling trying to get the result by chapters. I need a Dax method to do so.
The point is, my table in addition to projects it also has chapters, so each project has many chapters. To do the table with total amounts per project I did:
Table 2 = SUMMARIZE('Table','Table'[Project ID],"Version",MAX('Table'[Version]))
Then add a new column in this table
Sales = SUMX(FILTER('Table','Table'[Project ID]='Table 2'[Project ID]&&'Table'[Version]='Table 2'[Version]),'Table'[Sales])
As I mentioned before that table gave me the total amount of the latest version of each project as I asked for, but now I would like to know how to make that table (or another one) taking into account the chapters as well. So as an example the original table is like:
Project ID | Version | Chapter | Sales |
1 | 0 | 01.01 | 10 |
1 | 0 | 01.01 | 15 |
1 | 1 | 01.01 | 15 |
1 | 1 | 01.02 | 20 |
1 | 1 | 01.02 | 15 |
1 | 2 | 01.01 | 30 |
1 | 2 | 01.01 | 30 |
1 | 2 | 01.02 | 50 |
2 | 0 | 01.01 | 20 |
2 | 0 | 01.02 | 20 |
2 | 1 | 01.01 | 100 |
2 | 1 | 01.02 | 40 |
2 | 1 | 01.02 | 70 |
And the result I would like to get would be like:
Project ID | Version | Chapter | Sales |
1 | 2 | 01.01 | 60 |
1 | 2 | 01.02 | 50 |
2 | 1 | 01.01 | 100 |
2 | 1 | 01.02 | 110 |
Thank you very much in advance
Solved! Go to Solution.
Hi @Enric21
Try this measure :
Sale_m =
VAR _version =
CALCULATE(
MAX( 'Table'[Version] ),
ALLEXCEPT( 'Table', 'Table'[Project ID] )
)
RETURN
CALCULATE( SUM( 'Table'[Sales] ), FILTER( 'Table', [Version] = _version ) )
drag these fields in the table visual but replace the Sale with above measure.
And the result:
I put my pbix file in the attachment 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 @Enric21
Try this measure :
Sale_m =
VAR _version =
CALCULATE(
MAX( 'Table'[Version] ),
ALLEXCEPT( 'Table', 'Table'[Project ID] )
)
RETURN
CALCULATE( SUM( 'Table'[Sales] ), FILTER( 'Table', [Version] = _version ) )
drag these fields in the table visual but replace the Sale with above measure.
And the result:
I put my pbix file in the attachment 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.
@Enric21 , Try a measure like
measure =
var _1 = maxx(allselected(Table), Table[project Id] = max(Table[project Id])) ,[Version])
return
calculate(sum(Table[sales]), filter(Table, Table[Project ID] =_max))
Hi,
I tried to do this measure but it didn't work for me, I got an error in [Version]): var _1 = maxx(allselected(Table), Table[project Id] = max(Table[project Id])) ,[Version])
I manage to solve my question in Power Query with the @v-jingzhang help. Thank you all I am very grateful recieving your support.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |