Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    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.

  • mrothschild's avatar
    mrothschild
    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),
)
mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
7 years ago

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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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's avatar
    mrothschild
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

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

  • mrothschild's avatar
    mrothschild
    Icon for Continued Contributor rankContinued Contributor
    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),
)
    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      7 years ago

      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.