Question about how to use field in DAX

[qd96xuweifeng]

Hello,

I encountered one challenge on my power BI report design recently, could anyone please advise on it?

My source summary table looks as below, but is with very large size.

ym_id	DIGIT_USER	MOB_USER	…	lndg_tot_bal	bcn_scr	…	customer
202211	0	0	…	5040830	9160	…	14
202211	0	0	…	3315879	7740	…	11
202211	0	0	…	480094.2	662	…	1
202211	0	0	…	1089981	717	…	1
202211	0	0	…	8083.89	862	…	1

1. YM_ID: This is a month indicator.
2. Segments Variables: There are around 33 distinct segments variables, and all of them are with binary values (“Yes” or “No”). Two samples are listed below:
   • Digit_User: Indicator of digital devices (Both Cell phone and computer) user.
   • MOB_User: Indicator of mobile device (Cell Phone) user.
3. Attributes Variables: There are more than 30 distinct attributes variables, and most of them are with numerical values. Three samples are listed below:
   • lndg_tot_bal: Total sum of lending balance within these specifical categories.
   • MOB_User: Total sum of beacon score within these specifical categories.
   • Customer: number of customer within these specifical categories.

I have generated around 30 new measurements based on those attributes variables, and two examples are as below:

• Average_Beacon_Score = divide(sum(table[bcn_scr]),sum(table[customer]))
• Average_Lending_Balance = divide(sum(table[lndg_tot_bal]),sum(table[lndg_tot_bal]))

The next step, I create two fields as below to group both Segments Variables andAttributes Variables. The use the two fields to control the final matrix.

Segment fields: This is used to select the segment variable for comparison, such as selecting comparison based on “Digit_User” or “Mob_User”.

Attributes fields: This field includes all 30 different measurements from the corresponded attributes, such as “Average_Beacon_Score” and “Average_Lending_Balance”.

Based on above design, I can get a matrix as below:

• When I select two months (202212 and 202303) in the filter, I get the compare months.
• The “Yes” and “No” values are from segment (Comparison Categories) filter which controled by Segment field, which is limited to single selection, and I select “Digtal_User” as example below.
• The measurements attributes are selected for “Beacon score” and “Lending Balance” from attribute field in this example. I can also include the others or all of these 30 attributes.

Then I get the matrix as below with “Switch values to row” option.

So far, everything looks good. But I am struggled on creating “Level Difference” and “% difference” comparison within and without category comparison. For example, in month of 202212, if I would like to get difference of average beacon score between digital users (653.11) and non-digital user (549.88), I always failed on it. I think this can be done by DAX, but not clear how to apply DAX on fields rather than single column.

Q1: So, could anyone please help to advise if field can be used in the measurement to handle multiple attributes together? Especially how to use field in filter within the measurement?

Q2: May I know if power BI also provide some advance data transpose method? For example, I can transpose the 30 attributes variables (eg. Beacon Score & Lending Balance) by the 33 segments columns (Digit_User, MOB_User and YM_ID).

Any advice will be appreciated!

Thanks a lot!

Wayne