Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to count the months that contain values to calculate the average spending.
The attached photo shows the months that contain values.
The target is to get the average spending like this (114,225 / 7 = 16,318).
Please help me to solve this issue>
Thank you in advance.
Solved! Go to Solution.
Hi @aamulhim
Try these:
with a separate dates table
=
CALCULATE (
DISTINCTCOUNT ( dates[month] ),
FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)
no dates table
=
CALCULATE (
DISTINCTCOUNT ( 'dates'[month] ),
FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)
if none works, please share a sample data (not an image) and your expected result.
Proud to be a Super User!
Hi @aamulhim
Try these:
with a separate dates table
=
CALCULATE (
DISTINCTCOUNT ( dates[month] ),
FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)
no dates table
=
CALCULATE (
DISTINCTCOUNT ( 'dates'[month] ),
FILTER ( 'table', NOT ( ISBLANK ( 'table'[value] ) ) )
)
if none works, please share a sample data (not an image) and your expected result.
Proud to be a Super User!
Thank you so much Dane. It works.
Best regards,
Hi,
It will depend up on the ganularity of your data. Share the download link of the PBI file.
Hi
Thank you for your reply. I have attached a screenshot from the data view, I hope that will help.
Hi,
An image does not help. Please share the download link of the PBI file.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |