Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnM
Regular Visitor

How to add a fixed percentage assessment fee to a sub-total

I have an organization budget consisting of many departmental budgets rolled into one.  I need to assess a 10% fee from the income from each department.  My columns are Department, Account, then each month of the year and a total.  I need to see the 10% subtracted from each amount under the pertinent month.  Here is a sample of my data:

incomesampledata.jpg

 

Thanks!


For example, January's Total should have 10% subtracted from it. 145,364-10% or 145,364-14,536
1 ACCEPTED SOLUTION

@JohnM well it should be pretty close what you are looking for, happen to take advantage of Nov PowerBI Update. YaY

 

budget.PNG



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.

View solution in original post

9 REPLIES 9
JohnM
Regular Visitor

I'm still struggling with this a bit. Here is a link to sample data if anyone wants to tackle it... SampleData Excel File  Again, I need to assess 10% from everyone each month and know what that amount is so I can later add it to the General Fund's income.

 

Thanks!

I copied you since you first had a look.  I appreciate any help you can give.

 

@v-jiascu-msft

@parry2k

@JohnM is this what you are looking for?

 

budget.PNG



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.

@parry2k Wow! You work fast!  Thanks again for helping out!

 

You almost have what I need.  I don't need the 10% from each account -- just from the monthly total at the bottom.

 

Sorry if you received this twice -- I am having problems with the Reply form.

@JohnM taking of 10% from total is not an issue but  table total will not make sense, see image below. We have full amount of each month and then total is budget - 10% 

 

If you are expecting two lines for total, one with actual budget and other with 90% , we cannot do that and in that case we need to add two visual. how you expect to see final output?

 

budget.PNG



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.

"If you are expecting two lines for total, one with actual budget and other with 90% , we cannot do that and in that case we need to add two visual. how you expect to see final output?"

 

@parry2k I think I might need to go with the above (two visuals).  If you don't mind, how did you do the example with the 10% from each account and total?  I might be able to make that work - but not sure.

@JohnM i created a calculated table and try to put together both in one table. I will soon send pbix and you can pick and choose.

 

budget.PNG

 

 



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.

@JohnM well it should be pretty close what you are looking for, happen to take advantage of Nov PowerBI Update. YaY

 

budget.PNG



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.

v-jiascu-msft
Employee
Employee

Hi @JohnM,

 

Based on your data here, please try the measure below. If you need more details, please share a sample file.

Measure =
IF (
    ISFILTERED ( Table1[Account] ),
    SUM ( Table1[Total] ),
    SUM ( Table1[Total] ) * 0.9
)

How-to-add-a-fixed-percentage-assessment-fee-to-a-sub-total

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@JohnM it will be best practice to unpivot your data then there will be simple DAX calculation and you will able to create this matrix the way you want.

 

Let me know if you need further help, if possible share sample data in excel and can do the solution for you.



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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.