Forum Discussion
Sum by group By and Max
please lok into the caluclated formula. it may help
Resident RockstarHi 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
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
Community Champion
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 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
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 :/
- 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
