Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vikas_patel81
Regular Visitor

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

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.

11111.png

 

But when I turn on the subtotals, it is coming summary of all text values & not the count. (See in the image below)

222222.png

 

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. 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7
v-xiaotang
Community Support
Community Support

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.

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

This is all i see when i open the file.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

I cannot share my e-mail address.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.