Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello,
This is my firt post ; Thank you for having me I've been lurking around here for some time and today I don't know how to solve this problem. I'm still very new at PBI and still reading training books on PBI and DAX.
I am currently working on a project where I'm trying to compute monthly average for:
- each item (item ID#)
- each particular month (I created a Month-year column - some item only stay online for a few months some keep going until the end of the year)
- summing up all expense codes in the monthly average cost but have the ability to filter monthly average by expense code to compare later in the dashboard.
So ideally I would be able to:
- calculate monthly average for each ItemID
- filter by expense code (to remove or add some of them as needed)
Here is my sample table
ID | Date | Month-year | Expense Code | Total |
1 | 2021-06-15 | 6-2021 | Code1 | 600 |
1 | 2021-06-15 | 6-2021 | Code1 | 40 |
1 | 2021-06-15 | 6-2021 | Code2 | 25 |
1 | 2021-06-15 | 6-2021 | Code2 | 2 |
1 | 2021-06-15 | 6-2021 | Code2 | 1 |
1 | 2021-06-15 | 6-2021 | Code3 | 250 |
2 | 2021-05-23 | 5-2021 | Code3 | 10 |
2 | 2021-05-15 | 5-2021 | Code1 | 550 |
2 | 2021-05-15 | 5-2021 | Code1 | 45 |
2 | 2021-05-15 | 5-2021 | Code2 | 40 |
2 | 2021-05-15 | 5-2021 | Code2 | 4 |
2 | 2021-05-15 | 5-2021 | Code2 | 2 |
2 | 2021-05-15 | 5-2021 | Code3 | 175 |
2 | 2021-05-14 | 5-2021 | Code3 | 70 |
2 | 2021-05-02 | 5-2021 | Code3 | 50 |
and my formula
Monthly cost =
SUMX(
FILTER (
table,
table[ID] = EARLIER ( table[ID] )
&& DATEDIFF ( 'table'[Month-year], EARLIER ( 'table'[Month-year] ), MONTH ) = 0
),
table[Total]
)
It's not too bad but some of the costs are wrong while some are right so I suspect that the costs breakdown are not working too well.
Thank you all for your help!
Solved! Go to Solution.
Hi @Anonymous ,
Create a measure as below;
Measure =
VAR _count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
)
)
VAR _sum =
CALCULATE (
SUM ( 'Table'[Total] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
)
)
RETURN
DIVIDE ( _sum, _count, BLANK () )
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
@Anonymous , Sumx or averagex as per need
A new column
Monthly cost =
AverageX(
FILTER (
table,
table[ID] = EARLIER ( table[ID] )
&& 'table'[Month-year] = EARLIER ( 'table'[Month-year] )
),
table[Total]
)
Hello,
So this formula is indeed calculating correct monthly cost as a whole but it is not able to "dynamically" recalculate the monthly cost when filtering by expense code from the report view. It does perform some calculation but the result is not correct - for instance it's telling me I pay more taxes as monthly cost than rent cost total which is not true.
Is it coming from the formula or maybe the way I setup the filter on the report page?
Again thank you for the help - at least it solved 50% of the problem.
Hi @Anonymous ,
Create a measure as below;
Measure =
VAR _count =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
)
)
VAR _sum =
CALCULATE (
SUM ( 'Table'[Total] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = MAX ( 'Table'[ID] )
&& 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
)
)
RETURN
DIVIDE ( _sum, _count, BLANK () )
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
User | Count |
---|---|
94 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |