Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I've got a problem with a matrix subtotals in the report.
For # of transaction column calaculation getting wrong subtotals in matrix table...I used some calcualtion for getting the # of transaction column.These column is used to calcualte count values.
VAR _Count1 =CALCULATE(DISTINCTCOUNT('table'[policy_or_group_reference]),FILTER('table',('table'[Transaction type] = "New Business" || 'table'[Transaction type] = "New MBR" || 'table'[Transaction type] = "New Reporter") && 'table'[endt_cert] = 0))
VAR _Count2 =CALCULATE(COUNTROWS(SUMMARIZE('table',
'table'[policy_or_group_reference] ,
'table'[endt_cert] )),FILTER('table',('table'[Transaction type] = "Endorsements" || 'table'[Transaction type] = "Cancellation" || 'table'[Transaction type] = "Reporter Endt") && ('table'[endt_cert] > 0 || 'table'[endt_cert] = 0)))
RETURN
if(isblank(_Count1), _Count2, _Count1)
These is the calcualtion i used while creating report...
Here ,total is calcualting for 3 type only ie..New Business,New MBR,New Reporter only...balance is 3 type total is not considering....
21+1+3=25 getting which is not total for all type...
Plse help?
Solved! Go to Solution.
Hi @NISHA_S ,
You can create another new measure as below base on the current measure [# of transaction], then put it on your matrix to replace the measure [# of transaction] .
Measure = SUMX ( GROUPBY ( 'table'[MTD], 'table'[Transaction type] ), [# of transaction] )
In addition, you can refer the content in the following links to resolve your problem.
Power BI Shows Incorrect Measure Total? How to fix it?
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Best Regards
@NISHA_S , change return like give below, replace with correct un summarized columns in visual
sumx(summarize(Table, Table[MTD], Table[By transaction], "_1", if(isblank(_Count1), _Count2, _Count1)),[_1])
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |