I have listed Text in Matrix as values, but the total is not showing count of values.

[vikas_patel81]

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. 

[v-xiaotang · Accepted solution]

Hi @vikas_patel81 

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.

[vikas_patel81]

Hi,

Thank you very much.

This worked, I've got the total count in matrix now!

Just made a little modification.

List of customField.QHP Code values planned 2 =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('visileanQHP'[customField.QHP Code])

VAR __MAX_VALUES_TO_SHOW = 35

var _count = DISTINCTCOUNT('visileanQHP'[customField.QHP Code])

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)

[Ashish_Mathur]

Hi,

I would like to try.  Share the link from where i can download your PBI file.

[vikas_patel81]

https://drive.google.com/file/d/1WuaEGMarVVOWClaXTHx9GPEWjMQtXxND/view?usp=sharing

[Ashish_Mathur]

Hi,

This is all i see when i open the file.

[Ashish_Mathur]

Hi,

I cannot share my e-mail address.

[vikas_patel81]

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.