% calculation not correct in totals

[Reetz]

Not sure if this is even possible. 

My measure:

Usage %=IFERROR (IF( product 1>0, DIVIDE ( product 2, product 1) ,0), " - " )

My 2 issues are: 

1.  if product 1 =0 and product 2 >0, I want to show product 2 at the row level, but not have it included in the total.  

2.  for the Usage % in the Total line,  i want to include only those accounts that have Product 1 orders >0 and Product 2 orders >=0.  (The calculation would be 40/95=42.1%)

Thanks in advance!

[sturlaws]

Hi @Reetz 

in your screenshot line two changes from 12 to 40. I assume this is a typo.

You can use this dax to solve it:

usage % =
VAR _tmp =
    DIVIDE (
        SUMX ( someTable, IF ( someTable[Prod1] > 0, someTable[Prod2], 0 ) ) + 0,
        SUMX ( someTable, IF ( someTable[Prod1] > 0, someTable[Prod1], 0 ) )
    )
RETURN
    IF ( ISBLANK ( _tmp ), "-", _tmp )

As for the product 2-column, this has to be converted to a measure to achieve the desired output:

Measure Prod 2 =
IF (
    HASONEVALUE ( someTable[Prod2] ),
    SUM ( someTable[Prod2] ),
    CALCULATE (
        SUM ( someTable[Prod2] ),
        VAR _tmp =
            CALCULATETABLE (
                VALUES ( someTable[Act] ),
                FILTER ( someTable, someTable[Prod2] > 0 && someTable[Prod1] > 0 )
            )
        RETURN
            FILTER ( someTable, someTable[Act] IN _tmp )
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.