Search code examples
powerbidaxssas-tabular

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

I have the following schema :

enter image description here

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.

Solution

  • 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 )