Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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] ) ) ) )
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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] ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |