The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
PBIX file : https://1drv.ms/u/s!Ag9tIyk2ofNRjlBFygNBL3s9VG2N?e=qoIeBc
Excel file: https://1drv.ms/x/s!Ag9tIyk2ofNRjk9-uND6F2ZLZgaZ?e=oAWQzt
I have revenue table which includes service name and all, and expense table which include service expense
to calculate percentage, i need to do servicerevnue / total_revenue
then service expense is calculated_percentage * total-service_expense
when i select for example october 4:
and when i select october 5:
and when i select both:
as you see, lets take service A:
on october 4, expense is 163.58 and gross is 1,041.42
on october 5, expense is 32.63 and gross is 376.47
so if i select october 4 and october 5 together:
i should get for expense = 196.21 and gross = 1417.99
but as photo above states, i got the numbers wrong
please help
Solved! Go to Solution.
I provided my explanation in my first reply. Attached the solution for the example yoy have provided in this post.
Hi @eliasayy
Please refer to attached sample file with the solution
Taxes =
SUMX (
VALUES ( 'Calendar'[Date] ),
CALCULATE (
SUM ( Expense_Table[Total] ) * MAX ( New_Tax_Rate_Table[Percentage] )
)
)
@tamerj1 why is it when i do the calculations seperatly it works but when i make them into one measure and use Var it doesnt give the same answer?
I provided my explanation in my first reply. Attached the solution for the example yoy have provided in this post.
@tamerj1 thank you very much i appreciate your help
for somereason it works on the sample data but i did the same exact calculation and method on my work project but doesnt work for some reason
@eliasayy
Perhaps it has a different filter context. Maybe you can share more details to assist you further.
Thank you veey much but this is another post for taxes and this one is for the expense can you please help out
Sure. However, it is the same approach; you need to iterate over the the selected dates one by one, perform the calculation for each date then sum the individual results for all the selected dates.
what is the formula for Expanses?
@tamerj1 it is included in the powerbi file but this is the formula
ServicerevenueA = CALCULATE(SUM(revenue_table[total]),revenu_table[service]= "Service A")
Total revenue = SUM(revenue_table[total])
PercentageA = [servicerevenueA]/[total revenue]
Service expense = CALCULATE(SUM(expense_table[total]),expense_table[eligible for service revenue] = "true")
ExpenseA = [PercentageA] * [serviceexpense]
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
36 | |
21 | |
18 | |
15 |
User | Count |
---|---|
124 | |
39 | |
30 | |
24 | |
22 |