Forum Discussion

GregSher's avatar
GregSher
New Member
1 year ago
Solved

% of Income on Total Lines

I recently had a problem solved with how to add a % of income column in a Matrix.

 

https://community.fabric.microsoft.com/t5/Desktop/Adding-a-of-Revenue-column-to-a-P-amp-L/m-p/4259299#M1338276

 

Now my problem is that the measure doesn't show for the total line.

 

How do I get 55.7% to show on the total line (i.e. get the measure to continue to calculate on the total line).

 

Is there a setting that would make that happen?  Also, I'd be ok if the % was the same sign at the numbers (i.e. the expenses are negatives) and then the total line just added the subtotals.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thank you rubayatyasmin

    Hi, GregSher 

    According to the screenshot you provided, you have formatted the % column to convert the negative value to a positive value, as shown in the following image:

    You can use the following logical expression to determine whether it is the last row to add up to the row, and then execute the corresponding algorithm:

    Is total row = IF(ISINSCOPE('Table'[Sub Type]) || NOT HASONEVALUE('Table'[Type]),IF(HASONEVALUE('Table'[Type]),1,0))

    Normally, if your amount column has a negative value then the total will continue to calculate and show 55.7, here is the metric I used:

    %1 = DIVIDE([Amount measure],SUMX(FILTER(ALLSELECTED('Table'),'Table'[Type] = "Income"),'Table'[Amount]))
    %2 = 
IF(ISINSCOPE('Table'[Sub Type]) || NOT HASONEVALUE('Table'[Type]),
IF([%1]<0,-[%1],[%1]))

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you rubayatyasmin

    Hi, GregSher 

    According to the screenshot you provided, you have formatted the % column to convert the negative value to a positive value, as shown in the following image:

    You can use the following logical expression to determine whether it is the last row to add up to the row, and then execute the corresponding algorithm:

    Is total row = IF(ISINSCOPE('Table'[Sub Type]) || NOT HASONEVALUE('Table'[Type]),IF(HASONEVALUE('Table'[Type]),1,0))

    Normally, if your amount column has a negative value then the total will continue to calculate and show 55.7, here is the metric I used:

    %1 = DIVIDE([Amount measure],SUMX(FILTER(ALLSELECTED('Table'),'Table'[Type] = "Income"),'Table'[Amount]))
    %2 = 
IF(ISINSCOPE('Table'[Sub Type]) || NOT HASONEVALUE('Table'[Type]),
IF([%1]<0,-[%1],[%1]))

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.