Row Total that moves with sort on average

[PeterL1]

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

GROUPS	SQM	SALES	AVERAGE PER SQM
Group9	688	800000	1,162.8
Group5	164	150000	914.6
Group17	256	230000	898.4
TOTAL	2922	2535000	867.6
Group20	71	60000	845.1
Group11	215	180000	837.2
Group4	72	60000	833.3
Group14	6	5000	833.3
Group16	132	110000	833.3
Group19	168	140000	833.3
Group1	123	100000	813.0
Group12	123	100000	813.0
Group7	248	200000	806.5
Group10	62	50000	806.5
Group2	114	90000	789.5
Group15	89	70000	786.5
Group8	54	40000	740.7
Group3	95	70000	736.8
Group13	48	30000	625.0
Group6	68	40000	588.2
Group18	126	10000	79.4

[Zubair_Muhammad · Accepted solution]

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]

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]

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]

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

[Zubair_Muhammad · Accepted solution]

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

[Zubair_Muhammad · Accepted solution]

Hi @PeterL1