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 everybody,
Basically I have to make a comparison between incomes and revenues.
The column that I am trying to calculate through a measure is revenues.
I need the value filtered only for the column and not for the row (it is calculated by summarizing the invoice amounts, that are per row).
I need this to make a comparison between every incomes and revenues of the period for every aging range. So, the result that I want to get is the same revenue value per each row filtered only by invoice date (the column). After that, I have created another measure which is the comparison % between the row incomes and the calculated revenue.
I have tried with:
Revenue = CALCULATE (SUM (Payments [Invoice Amount]); ALLSELECTED (Payments)), but I get the result in the screenshot
Revenue = CALCULATE (SUM (Payments [Invoice Amount]); ALLEXCEPT (Payments; Payments [Invoice Date])), but I've obtained the same result.
Do you have any suggestions?
Visual configuration
This is what I've have obtained:
Thanks to everybody
Solved! Go to Solution.
I've found the solution:
I've found the solution:
I've (near) obtained the required result by creating this misure:
@Dario87 , As revenue might not have age and might work with invoice date, I would like see how the data is placed
Can you share sample data and sample output.
Hi @amitchandak ,
thank you for your fast reply.
Here some sample rows of my dataset:
Id | Delay Days | Aging | Invoice Date (yyyy-MM-dd) | Invoice Amount | Income |
1 | 2 | <=30 | 2018-01-23 | 50.00 | 50.00 |
2 | 45 | 30 <= 60 | 2017-07-03 | 34.00 | 34.00 |
3 | 78 | 60 <= 90 | 2019-04-21 | 12.00 | 12.00 |
4 | 5 | <= 30 | 2020-05-03 | 45.00 | |
5 | 0 | <= 30 | 2017-09-09 | 25.00 | 25.00 |
And what I expect from my real table is to have 277,478,993.23 for each row of Fatturato 2017, 345,705,749.02 for each row of Fatturato 2018, ... including the total row
Here the result using the simple data set data
Let me know if you need any other information.
Regards,
@Dario87 ,
I would suggest you create another date table named 'Calendar' and make relationship between 'Calendar' and 'Payments' table, then create slicer based on the date column in 'Calendar' table and create a measure using dax as below:
Revenue =
CALCULATE (
SUM ( Payments[Invoice Amount] );
FILTER (
ALLEXCEPT ( 'Payments'; 'Payments'[Aging] );
'Payments'[Date] IN VALUES ( 'Calendar'[Date] )
)
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yuta-msft ,
thank you for your reply.
Anyway I don't think that your solution fits my goal.
You suggest to use a slicer, but my purpose is to visualize (not filter) the revenues per column value (Invoice Date).
So I won't use a slicer.
Regards,
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |