SUM the amount of products with Available status and divide it by the sum of total products with the status Available

Question

I have the following schema :

I am using a live connection to this SSAS Tabular cube, I want to calculate the amount of the Products with Status = Available

for each product comparing to the total amount of Products with Status=Available.

The dax query is like below :

Montant =
VAR MontantA = 
    CALCULATE ( 
        SUM(FactTable[montant]),
                 
        dim_status[labelstatus] = "Available" ,
        FactTable[ProductSK] <> -1

)
    )

VAR MontantTotal = 
    CALCULATE (
    SUM(FactTable[montant]),
        dim_status[labelstatus] = "Available" 
    )
RETURN
    DIVIDE ( 
        MontantA,
        MontantTotal
    )

I get 1 as a result for all the products.

So the first sum is for a specific product, and the second sum is for all products? — TheRizza, Jan 19 '22 at 18:14

score 2 · Accepted Answer · answered Jan 20 '22 at 15:16

I think this might get you a bit closer to what you're after:

Montant =
VAR MontantA =
    CALCULATE (
        SUM ( FactTable[montant] ),
        dim_status[labelstatus] = "Available",
        KEEPFILTERS ( dim_product[ProductSK] <> -1 )
    )
VAR MontantTotal =
    CALCULATE (
        SUM ( FactTable[montant] ),
        dim_status[labelstatus] = "Available",
        ALL ( dim_product )
    )
RETURN
    DIVIDE ( MontantA, MontantTotal )

SUM the amount of products with Available status and divide it by the sum of total products with the status Available

1 Answers1