Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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