Forum Discussion

VistaDee's avatar
VistaDee
Frequent Visitor
4 years ago
Solved

Help with % in Matrix

Hi There

 

I have a matrix and it is made of as follows:

  • A SubOrderID (Unique ID)
  • Products
  • Invoice Total (this is a custom column that looking up the SubOrder & Product combo in another table and pulls back the invoice total: 
    CALCULATE(SUM('Invoices Totals'[Invoices Total]), FILTER(ALL('Invoices Totals'), 'Orders Total'[SubOrderID] = 'Invoices Totals'[SubOrderID] && 'Orders Total'[Product] = 'Invoices Totals'[Product]))
  • Purchases Total (same as Invoice Total)
  • Margin % : A custom column that works out as follows:  DIVIDE(('Orders Total'[Invoices Total] - 'Orders Total'[Purchases Total]), 'Orders Total'[Order Total])

 

Then I created a Matrix and added all these columns but my percentages arent coming out right. Its Summing them and I dont want to do it. The Average doesnt work either as the breakdown of Order/Product throws that out as well.... Ay ideas?

 

MATRIX WITH SUM OF %

 

MATRIX WITH AVG OF %

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi VistaDee 

    I suggest you to build a Margin% measure based on your Margin%  custom column. Try HASONEVALUE function to show results you want in subtotal.

    The measure should looks like as below.

     

    Margin% Measure =
VAR Total_Invoices =
    SUMX ( ALL ( 'Invoices Totals' ), 'Invoices Totals'[Invoice Total] )
VAR Total_Purchases =
    SUMX ( ALL ( 'Purchases Total' ), 'Purchases Total'[Purchases Total] )
VAR Total_Order =
    SUMX ( ALL ( 'Orders Total' ), 'Orders Total'[Orders Total] )
VAR Subtotal =
    DIVIDE ( Total_Invoices - Total_Purchases, Total_Order )
RETURN
    IF ( HASONEVALUE ( [SubOrderID] ), [Margin%], Subtotal )

     

    If this reply still couldn't help you solve your problem, please share a sample with me. And you can show me a screenshot of the result you want in subtotal. This will make it easier for me to understand your requirement.

     

    Best Regards,
    Rico Zhou

     

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi VistaDee 

    I suggest you to build a Margin% measure based on your Margin%  custom column. Try HASONEVALUE function to show results you want in subtotal.

    The measure should looks like as below.

     

    Margin% Measure =
VAR Total_Invoices =
    SUMX ( ALL ( 'Invoices Totals' ), 'Invoices Totals'[Invoice Total] )
VAR Total_Purchases =
    SUMX ( ALL ( 'Purchases Total' ), 'Purchases Total'[Purchases Total] )
VAR Total_Order =
    SUMX ( ALL ( 'Orders Total' ), 'Orders Total'[Orders Total] )
VAR Subtotal =
    DIVIDE ( Total_Invoices - Total_Purchases, Total_Order )
RETURN
    IF ( HASONEVALUE ( [SubOrderID] ), [Margin%], Subtotal )

     

    If this reply still couldn't help you solve your problem, please share a sample with me. And you can show me a screenshot of the result you want in subtotal. This will make it easier for me to understand your requirement.

     

    Best Regards,
    Rico Zhou

     

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