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.
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]
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |