Forum Discussion

PeterL1's avatar
PeterL1
Helper I
8 years ago
Solved

Row Total that moves with sort on average

Hi,  I am currently trying to create a row total that would move when sorted alphabetically by an average.   The total needs to move with the Groups when i sort the Average per SQM, so that i ca...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi PeterL1

     

    Try this

    Go to Modelling Tab>>>>>NEW TABLE

     

    NEW Table =
UNION (
    YourTable,
    ROW (
        "GROUPS", "TOTAL",
        "SQM", SUM ( YourTable[SQM] ),
        "SALES", SUM ( YourTable[SALES] ),
        "AVERAGE PER SQM", DIVIDE ( SUM ( YourTable[SALES] ), SUM ( YourTable[SQM] ) )
    )
)
MFelix's avatar
MFelix
Super User
8 years ago

Hi PeterL1,

 

Why do you need to have the total on your table?

 

I would do a mesaure to highlight the groups below or up:

 

Average = DIVIDE( SUM(Groups[SALES]), SUM(Groups[SQM]))

Group below =
IF (
    [Average] < CALCULATE ( [Average], ALLSELECTED ( Groups[GROUPS] ) ),
    "Below average",
    BLANK ()
)

In this case you can make this a variable value and interact with a slicer instead of fixing your table to one value:

 

 

Regards,

Mfelix

 

  • PeterL1's avatar
    PeterL1
    Helper I
    8 years ago

    Hi MFelix,

     

    That is the way that we have been analysing reports where i work for a long time, however this has been done in Excel where it is easy to sort by a made total.

    Dont want to confuse people by changing a report around that they are used to.


    Thanks

    Peter

     

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi PeterL1,

       

      Just giving you different options in the PBI. You are moving to a BI platform and the way of interacting with the reports is different in concept and in form, but accept that you don't want to confuse people.

       

      It's a mind set that is difficult to change.

       

      Regards,

      MFelix