help with unflattened data

[mrothschild]

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 and theoretically ease future programming.  

This measure from my flattened data produces desired results:

CALCULATE(

    SUM('FlatTable'[Base]),

    ALLSELECTED('FlatTable'[Asset ID]),

Filter('FlatTable','FlatTable'[Count]>0),

FIlter('FlatTable','FlatTable'[Count]<'FlatTable'[Lease Term (months)])

)

In my unflattened data, I have Asset ID linking two tables: SummaryInput and AssetReturn.  From above, [Base] and [Count] are on the AssetReturn table, but [Count] resets at 0 for each [Asset ID] and counts up to the highest number = [Lease Term (months)] 

[Lease Term (months)] is on the SummaryInput table, but not on the AssetReturn table.  

So I'm trying to figure out the right GROUPBY, SUMMARIZE, FILTER, etc. to arrive at the result I'm trying to achieve, which is the sum of all [Base] except for 0 and the last associated period for a given [Asset ID], i.e., if the data was like below, the desired output result measure would produce 100.5 = sum (3*10 + 5*12.5 + 2*4) 

Another way to think about this is to return a value of (150 + 200 + 65), which is a conditional MAX function on [Count], but it's the MAX only associated with a given [Asset ID] as opposed to the entire column.  Or, I assume, but haven't figured out how, I could re-flatten the data within Power BI, to add a column with [Lease Term (months)] to below and then compare [Count] and [Lease Term (months] as I did originally, but I assume that just moves me away from best practices again.

Asset ID	Count	Base
Alpha	0	-100
Alpha	1	10
Alpha	2	10
Alpha	3	10
Alpha	4	150
Bravo	0	-150
Bravo	1	12.5
Bravo	2	12.5
Bravo	3	12.5
Bravo	4	12.5
Bravo	5	12.5
Bravo	6	200
Charlie	0	-50
Charlie	1	4
Charlie	2	4
Charlie	3	65

[Greg_Deckler · Accepted solution]

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.

[Ashish_Mathur]

Hi,

I am confused about what result do you want - 100.5 or 415?

[mrothschild]

Either one will do.  I can subtract the 415 from the column total to get to the same result.

[Ashish_Mathur]

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.

[mrothschild · Accepted solution]

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

[d_gosbell]

Sorry for not replying sooner, something appears to be going on with notifications and I have not received any for the last 4 days.

I think the second solution you arrived at with the help of Ashish is a cleaner one, it's similar to the second suggestion I posted, I just did not have the correct name for one of your tables.

[mrothschild]

Thanks - but this produces 200, I need the sum of the maximum count at eachl AssetID

[d_gosbell]

I'm not sure if I'm missing anything, but I would have though you could do this with a calculation like the following

Measure = SUMX(
  AssetReturn,
  var _maxCnt = CALCULATE(MAX(AssetReturn[Count]), ALLEXCEPT(AssetReturn,AssetReturn[Asset ID]))
  return if( AssetReturn[Count] = _maxCnt, AssetReturn[Base])
)

Although you could probably match your original logic by using the related function to "lookup" the lease term in the SummaryInput table

Measure = SUMX(
  AssetReturn,
  var _leaseTerm = RELATED(SummaryInput[Lease Term (months)] )
  return if( AssetReturn[Count] > 0 && AssetReturn[Count] < _leaseTerm, AssetReturn[Base])
)

[mrothschild]

Thanks, but the first option you offered doesn't result in the desired output and the second throws an error that says: "The column SUmmaryInput[Lease Term. ..] either doesnt' exist or doesn't have a relaitonship to any table available in the current context."

I'm interested in the direction you're going because it seems like a much more transferrable approach than the solution that is currently working.

[Greg_Deckler · Accepted solution]

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.

[mrothschild]

Thank you.  This seems a bit brute force as well - but does work on my more complicated table. 

I'm not sure why someone suggested that unflattening my data would make programming easier - I don't think with a year's worth of dedicated effort I could have come up with your solution!  Thanks again.