Forum Discussion
[Need Help] How to Sum Values in subtotal based on Column/Row with CountDistinct formula
Please help my problem,
I calculate measure column distinct count of device_id with a formula:
#_D Mobile + Wifi = CALCULATE(
DISTINCTCOUNT(f_mw_202301_202404[device_id]),
FILTER(f_mw_202301_202404,
f_mw_202301_202404[category]= "Buy" || f_mw_202301_202404[category]= "Use"))
Value
I build visual Matrix and input #_D Mobile + Wifi in Values as image:
As shown, column subtotals and row subtotals are still distinct count (based on column and row contents). The expected result is column subtotals and row subtotals is the sum of the value of each column and row contents.
Thanks in advance.
Hi utsurur ,
You can try this measure.
#_D Mobile + Wifi = SUMX ( CROSSJOIN ( VALUES ( 'Table'[Matrix Row Field] ), VALUES ( 'Table'[Matrix Column Field] ) ), CALCULATE ( CALCULATE ( DISTINCTCOUNT ( f_mw_202301_202404[device_id] ), FILTER ( f_mw_202301_202404, f_mw_202301_202404[category] = "Buy" || f_mw_202301_202404[category] = "Use" ) ) ) )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
3 Replies
- Rupak_bi
Super User
If you use Measure, output will be like this only. To solve, First Create a summarize table with the same dax and then refer that table in matrix to get your desired result.
- xifeng_L
Hi utsurur ,
You can try this measure.
#_D Mobile + Wifi = SUMX ( CROSSJOIN ( VALUES ( 'Table'[Matrix Row Field] ), VALUES ( 'Table'[Matrix Column Field] ) ), CALCULATE ( CALCULATE ( DISTINCTCOUNT ( f_mw_202301_202404[device_id] ), FILTER ( f_mw_202301_202404, f_mw_202301_202404[category] = "Buy" || f_mw_202301_202404[category] = "Use" ) ) ) )Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
