Forum Discussion

PeterL1's avatar
PeterL1
Icon for Helper I rankHelper 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 can easially see what group is underperfoming. 

I cannot find any posts about this and i cannot get it to work myself.

There is not currently a total in my database, so i will have to make one up.

 

Any help is appreciated

 

GROUPSSQMSALESAVERAGE PER SQM
Group96888000001,162.8
Group5164150000914.6
Group17256230000898.4
TOTAL29222535000867.6
Group207160000845.1
Group11215180000837.2
Group47260000833.3
Group1465000833.3
Group16132110000833.3
Group19168140000833.3
Group1123100000813.0
Group12123100000813.0
Group7248200000806.5
Group106250000806.5
Group211490000789.5
Group158970000786.5
Group85440000740.7
Group39570000736.8
Group134830000625.0
Group66840000588.2
Group181261000079.4
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] ) )
    )
)

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    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
    Icon for Super User rankSuper 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
      Icon for Helper I rankHelper 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
        Icon for Super User rankSuper 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