Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

row subtotal have issue

Hi everyone, 

 

I've got a problem with a matrix subtotals in the report.

 

For # of Binders column calaculation getting wrong subtotals in matrix table...I used some calcualtion for getting the # of Binders column.These column is used to calcualte count values.

 

# of Binders =
VAR _count =
CALCULATE ( DISTINCTCOUNT ( 'table'[policy_or_group_reference] ), 'table'[endt_cert] = 0 )
VAR _percentage =
DIVIDE (
_count,
CALCULATE (
DISTINCTCOUNT ( 'table'[policy_or_group_reference] ),
FILTER (
ALLSELECTED ( 'table' ),
'table'[LLOYDS BINDER TYPE] = SELECTEDVALUE ( 'table'[LLOYDS BINDER TYPE] )
&& 'table'[endt_cert] = 0
)
),
0
)
RETURN
IF (
SELECTEDVALUE ( 'table'[LLOYDS BINDER TYPE] ) = "Percentage of Total",
ROUND (_percentage, 4 ) * 100 & "%" ,
_count & ""
)
 
These is the calcualtion i used while creating report...
I tried to use  these measure
subtotal_system = if(HASONEVALUE('table'[System type]),[# of Binders],SUMX(VALUES('table'[System type]),[# of Binders]))...
It fails because sumx cannot work with values of type string ...
Is there any othere solution for these?
Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    On a side note... You can't use measures that return text in formulas that expect numbers. Formatting numbers (including %) should be done by the UI, not measures themselves.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    The picture above cannot be enlarged.
    In the measure above, you don't need to add the percentage character. This can be done with formatting in Power BI. Select the measure and click the % under Measure tools.

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.