cross tab with more than fileds as rows section

[Anonymous]

Hi All,

I have a requirement to show data as cross tab with more than 1 rows in a table i have used matrix but its allowing me only one field.

req2 : i have values and respective status ,I wanted to display Y or N(if no status/blank) and count only Y in the total.

attached data sheets 

thanks in adavnce

Please find attached Pbx file and sample data 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

It is not possible to show a numeric count value in grand total row while display text values in detailed rows. As a workaround, we can display 1 to represent ACTIVE and 0 to represent INACTIVE/no STATUS,  then count only 1 in total row.

New a calculated table first. 

Data2 =
CROSSJOIN (
    CROSSJOIN (
        SUMMARIZE ( Data1, Data1[region], Data1[country], Data1[CTR] ),
        VALUES ( Data1[Type] )
    ),
    VALUES ( Data1[status] )
)

Add a calculated column in above table 'Data2'.

If Y/N =
VAR _checkStatus =
    CALCULATE (
        LASTNONBLANK ( Data1[status], 1 ),
        FILTER (
            Data1,
            Data1[region] = EARLIER ( Data2[region] )
                && Data1[country] = EARLIER ( Data2[country] )
                && Data1[CTR] = EARLIER ( Data2[CTR] )
                && Data1[Type] = EARLIER ( Data2[Type] )
                && Data1[status] = Data2[status]
        )
    )
VAR _checkSales =
    CALCULATE (
        LASTNONBLANK ( Data1[sale], 1 ),
        FILTER (
            Data1,
            Data1[region] = EARLIER ( Data2[region] )
                && Data1[country] = EARLIER ( Data2[country] )
                && Data1[CTR] = EARLIER ( Data2[CTR] )
                && Data1[Type] = EARLIER ( Data2[Type] )
                && Data1[status] = Data2[status]
        )
    )
RETURN
    IF ( _checkStatus <> BLANK () && _checkSales <> BLANK (), 1, 0 )

Use a Matrix to display data.

I have uploaded the sample .pbix file for your reference.

Best regards,

Yuliana Gu