Sum by group By and Max

[Anonymous]

Hello guys,

I know there are a lot a posts about that but I don't understand any of them a now I am lost with all these daxes fx; calculate, sumx, allexcept, summarize...

I just want to sum row sby group and max date and I don't succeed  to achieved that  following  any  post I have red.

So imagine: I have for each day few rows about a portfolio who containes instruments. It is a fact table so every day the sum of the market value of my portfolio is changing. If I sum instruments value today (let's say = 100k) tomorrow it could be 140K. What  I want is something like : sum market value group by portfolio name and max date. The sum of each instrument group by portfolio where date is the last one.

I tried but I don't understand the logic in dax measure 😕

Thx a lot for helping me to achieve that.

[v-piga-msft]

Hi @Anonymous ,

More details could help us understand your scenario better.

If it is convenient, could you share some sample data and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

Hi @v-piga-msft 

Thx for your answer! I found a part of the solution but still stuck to do something.Unfortunatly I cannot share data with you ( asset management client's data are really sensitive) but I will try to be as clear as spossible 🙂

So imagine a fact table with few columns -> DATE - FK_PORTFOLIO- FK ISNTRUMENT- MARKET VALUE

Everyday I recieve a position for each portfolio and portfolio has one or many instruments into it. And each instrument has a market value... look at the picture below  I create a trivial case of my problem but it should help I think.

What I want to summarize is the black part beacause it shows my lastest portfolio position with its sum market value =142.

In fact the first dashboard of my report show the lastest ( newest) position for a choosen portfolio and should be not affected by any slicer.

I hope it help you for helping me.

Thx a lot,

J.

[mussaenda]

Current Day Value = 
VAR currentYear =
 YEAR ( TODAY () ) 
RETURN
 CALCULATE (
 [New Orders],
 FILTER(ALLSELECTED(Your table here where date column is), YEAR(Your Date Here) = currentYear && Your Date Here = TODAY()))

Kindly check if this will work

[Anonymous]

@v-piga-msft 

Any idea?

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[Anonymous]

@v-piga-msft  Yes thx part of it! Thx a lot!

In fact what I am trying to do is a dashboard which resumes all my dashboards but with a page filter = where date is max 🙂 

Your example do the job for market value but I would like to show also the FK as a picture of the table ( a part of it) where date is max!

It should be something like summarizecolums where I would be able to show just all rows where date= max , group by all FK. Summerize is great but it creates a new table and then I have two Fact table ( I dont like that 🙂 )

Is There any way to filter a page by a measure ? I mean I am just doing that to avoid user manipulation because with a slicer if you take the last date ( = max date) and a portfolio--> all the numbers and metrics should be in line with the business.But the prob is when you arrive on the first page  each Portfolio or/and date are selected so it sums everything and it is wrong...

Hope I am clear enought 🙂

Thx a lot your help,

@mussaenda  sorry I haven't test your solution yet 😕

[Anonymous]

Hi @v-piga-msft  @mussaenda 

To be totaly clear what I want to show is not the FK but characteristic (from Dimension) related to that fact table.

So the sum for each  rows when date ( from my fact is max) and the colunm from dimensions such as portfolio name, instrument name...)

Thx a lot for helping 🙂

[Anonymous]

@v-piga-msft  @mussaenda 

nope? 😉

[tangutoori · Accepted solution]

please lok into the caluclated formula. it may help 

[Anonymous]

@tangutoori 

Thx it helps me despite it is not a measure,

But thx you !

J.

[v-piga-msft]

Hi @Anonymous ,

Sorry for the delay.

Please try the measure below.

Measure =
VAR maxdate =
    MAX ( 'Table1'[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[MARKET VALUE] ),
        FILTER ( 'Table1', 'Table1'[Date] = maxdate )
    )

Here is the output.

Hope this can help you!

Best  Regards,

Cherry