Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am planning to create a report where I need to show various calculations at the bottom of the tables as shown below.
E.g.
Region | Sales | Costs | Employees |
APAC | 60000 | 4000 | 100 |
EMEA | 30000 | 2000 | 200 |
USA | 20000 | 1000 | 300 |
Total | <TOTAL SALES> | <MEDIAN COSTS> | <AVERAGE EMPLOYEES> |
So , how can I start? shall I create a measure? but then how can I show it on the bottom like as in the table above?
Solved! Go to Solution.
Hi @Sachy123 ,
Based on my test, since for Sales , the total field is sum(Sales), you could use the default summarization type. And For Costs and Employees ,please try the following formula to create measures:
Costs1 = IF(HASONEVALUE('Table'[Costs]),SUM('Table'[Costs]),MEDIAN('Table'[Costs]))
Employees1 = IF(HASONEVALUE('Table'[Employees]),SUM('Table'[Employees]),AVERAGE('Table'[Employees]))
Costs2 = IF(ISINSCOPE('Table'[Region]),SUM('Table'[Costs]),MEDIAN('Table'[Costs]))
Employees2 = IF(ISINSCOPE('Table'[Region]),SUM('Table'[Employees]),AVERAGE('Table'[Employees]))
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Sachy123 ,
Based on my test, since for Sales , the total field is sum(Sales), you could use the default summarization type. And For Costs and Employees ,please try the following formula to create measures:
Costs1 = IF(HASONEVALUE('Table'[Costs]),SUM('Table'[Costs]),MEDIAN('Table'[Costs]))
Employees1 = IF(HASONEVALUE('Table'[Employees]),SUM('Table'[Employees]),AVERAGE('Table'[Employees]))
Costs2 = IF(ISINSCOPE('Table'[Region]),SUM('Table'[Costs]),MEDIAN('Table'[Costs]))
Employees2 = IF(ISINSCOPE('Table'[Region]),SUM('Table'[Employees]),AVERAGE('Table'[Employees]))
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Sachy123 yes you need to create measure and check in the measure if you are at the total line, and one way to do this is by using HASONEVALUE function:
Example measure:
Test Measure =
//in this example, if it is on total line it will calculate average otherwise sum
IF ( HASONEVALUE ( Table[Region] ), SUM ( Table[Costs] ), AVERAGE ( Table[Costs] ) ) )
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
80 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
98 | |
89 | |
82 | |
61 |