Add totals in Matrix when the data is UNICHAR character

[rflora]

Hi Folks,

I have a matrix visualization that uses a UNICHAR(10003) character to validate whether there is data or not in specific countries by Test (see image). The issue is with the totals where instead of a count of the data (with a number), it has the UNICHAR character as well. I don't see where i can change the type of data in the total to make it a count. 

Can someone help turn the total in a numerical sum? Thanks!

Rui

[PaulDBrown]

@rflora 

What is the measure you are using for the UNICHAR?

You could try:

New measure with totals =
VAR unirows =
    CALCULATE (
        COUNTA ( Table[Country] ),
        FILTER ( Table, NOT ( ISBLANK ( [your UNICHAR measure] ) ) )
    )
RETURN
    IF ( ISINSCOPE ( Table[Country] ), [your UNICHAR Measure], unirows )

[rflora]

Measure 2 = var a= COUNT(Merge1[Source Value]) return IF(ISBLANK(a),BLANK(),unichar(10003))

This is how my measure is built. How could it be twicked? 

Apologies but i don't have much knowledge in DAX.

[PaulDBrown]

@rflora 

Try this measure:

New measure with totals =
VAR unirows =
    CALCULATE (
        COUNTA ( Table[Country] ),
        FILTER ( Table, NOT ( ISBLANK ( [measure 2] ) ) )
    )
RETURN
    IF ( ISINSCOPE ( Table[Country] ), [measure 2], unirows )

 (where [measure 2] is the measure you currently have). Remove your [measure 2] from the visual and use this [New measure with totals].

[rflora]

New measure with totals = 
VAR unirows =
    CALCULATE (
        DISTINCTCOUNT('Test table'[Source Value] ),
        FILTER ('Test table', NOT ( ISBLANK ( [measure] ) ) )
    )
RETURN
    IF ( ISINSCOPE ( 'Test table'[Region]), [measure], unirows )

@PaulDBrown Thanks! I made a few changes to get closer to what i need. I added a distinct count since I have many different entries in my DB and also i updated the return to be the "Region" which is the higher level, making the total by test accurate.

The only thing missing now is to have the grand total by country as well:

Essentially i should have "1" in the blue arrows and "3" in the red arrows

[v-jingzhang · Accepted solution]

Hi @rflora

Do the Test1... Test3 come from another hierarchy column (assume it is called "Test") in your table? If so, try below measure:

New measure 3 = 
VAR unirows =
    CALCULATE (
        DISTINCTCOUNT ( 'TestData'[Value] ),
        FILTER ( 'TestData', NOT ( ISBLANK ( [Measure1] ) ) )
    )
RETURN
    IF (
        ISINSCOPE ( 'TestData'[Test] ) && ISINSCOPE ( 'TestData'[City] ),
        [Measure1],
        unirows
    )

Kindly let me know if this works.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[rflora]

Works perfectly! Thanks a mil.

[amitchandak]

@rflora , use is in scope and return formatted number, as datatype this measure is test

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/