Forum Discussion
mrothschild
7 years agoContinued Contributor
help with unflattened data
I'm new to BI and I've really twisted my head in knots here. To try to implement best practices, I'm moving from a flattened data table (from Excel) to unflatten my data to make it more efficient an...
- 7 years ago
This might be a bit convoluted, but seems to work:
Measure 4 = VAR __table = FILTER('Table7',[Count]>0) VAR __table1 = GROUPBY(__table,[Asset ID],"__max",MAXX(CURRENTGROUP(),[Count]),"__base",SUMX(CURRENTGROUP(),[Base])) VAR __table2 = ADDCOLUMNS(__table1,"__baseMax",MAXX(FILTER(ALL('Table7'),'Table7'[Asset ID]=EARLIER([Asset ID]) && 'Table7'[Count]=[__max]),[Base])) VAR __table3 = ADDCOLUMNS(__table2,"__baseFinal",[__base] - [__baseMax]) RETURN SUMX(__table3,[__baseFinal])See Table7 of attached.
- 7 years ago
Ashish - thanks to your help on a different question I had, I was able to figure out the intended result as follows. The RELATED function did the trick.
Thanks
CALCULATE( SUM(AssetReturnTable[Base]), ALLSELECTED(AssetReturnTable[Base]), FILTER(AssetReturnTable,AssetReturnTable[Count]>0), FILTER(AssetReturnTable,AssetReturnTable[Count]<RELATED(SummaryInputTable[Lease Term (months)])+1), )
Ashish_Mathur
7 years agoSuper User
Hi,
I am confused about what result do you want - 100.5 or 415?
- mrothschild7 years agoContinued Contributor
Either one will do. I can subtract the 415 from the column total to get to the same result.
- Ashish_Mathur7 years agoSuper User
Hi,
This is the measure i wrote
=Measure = SUMX(FILTER(SUMMARIZE(VALUES(Data[Count]),Data[Count],"ABCD",CALCULATE(MAX(Data[Count]),ALL(Data[Count])),"EFGH",SUM(Data[Base])),[ABCD]=Data[Count]),[EFGH])
Hope this helps.
- mrothschild7 years agoContinued Contributor
Thanks - but this produces 200, I need the sum of the maximum count at eachl AssetID