Get sales value from latest version of a project by chapters

[Enric21]

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

[v-chenwuz-msft · Accepted 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.

[amitchandak]

@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))

[Enric21]

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.