Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'am analyzing construction data for one of the project.
We've categorized milestone activities into 3 categories & have unique code for each activities(/milestone). I want to list all codes on weeks based on the planned completion date & actual completion & want to show total of milestone activities for each week at the last.
Now, I've used measures to list the milestone codes based on weeks & category in Matrix visual & it is perfectly as I want. See the image below.
But when I turn on the subtotals, it is coming summary of all text values & not the count. (See in the image below)
Can you help me to resolve this please?
Please check the measure, I've applied to list these codes in matrix -
List of customField.QHP Code values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('visileanQHP'[customField.QHP Code])
VAR __MAX_VALUES_TO_SHOW = 35
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
ASC
),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
)
)
Your help here will be really appriciated. Thank you.
Solved! Go to Solution.
You can try this,
List of customField.QHP Code values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('visileanQHP'[customField.QHP Code])
VAR __MAX_VALUES_TO_SHOW = 35
var _count = your count // not having your sample, so you need to modify here later, change it according to your needs.
var _string = IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
ASC
),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
)
)
RETURN IF(ISINSCOPE('visileanQHP'[QHP Type]),_string,_count)
If you need more help, please let me know. And please provide a sample file or some sample data, so that I can create a same pbix file.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
You can try this,
List of customField.QHP Code values =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('visileanQHP'[customField.QHP Code])
VAR __MAX_VALUES_TO_SHOW = 35
var _count = your count // not having your sample, so you need to modify here later, change it according to your needs.
var _string = IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
TOPN(
__MAX_VALUES_TO_SHOW,
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
ASC
),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
),
", etc."
),
CONCATENATEX(
VALUES('visileanQHP'[customField.QHP Code]),
'visileanQHP'[customField.QHP Code],
", ",
'visileanQHP'[customField.QHP Code],
ASC
)
)
RETURN IF(ISINSCOPE('visileanQHP'[QHP Type]),_string,_count)
If you need more help, please let me know. And please provide a sample file or some sample data, so that I can create a same pbix file.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you very much.
This worked, I've got the total count in matrix now!
Just made a little modification.
Hi,
I would like to try. Share the link from where i can download your PBI file.
Hi,
This is all i see when i open the file.
Hi,
I cannot share my e-mail address.
Hi Please provide your email. This is the private dataset connected with webdatabase. I need to provide you access & then share the file with you.
Thanks for your input.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
38 | |
30 |