Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Matrix - Calculation inside a matrix

I'm not sure how to explain my request clairly but I will try me best. I have to make this matrix - let's say on a User Table to simplify - On this table I have the subscription date (User[sub_dat...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

    Your "date_inscription is a Date Hierarchy ? If yes can you show me how it's made please ? 

     

    I used measures too, not calculated columns for "Inscrites Valides" and "avec Campagnes", I wanted to have the same model as yours to make something similar, and theyre made like the two you sent me earlier. I just displayed them in the table so its clearer to you. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Oops sorry Anonymous I forgot the most important : 

Can you try again with this one ? 

MonthPercentage = DIVIDE(
 COUNT('Associations'[id]),
 CALCULATE(COUNT('Associations'[id]),ALLEXPECT(Associations,Associations[date_inscription])),0)

 And if it's not exactly what you needed i guess you'll prefer 

MonthPercentage = DIVIDE(
 COUNT('Associations'[id]),
 CALCULATE(COUNT('Associations'[id]),ALLEXPECT(Associations,Associations[Groupe répartition activation])),0)

Please tell me if i failed again (I hope not)

Regards, Etienne

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you Anonymous for your help I know it's hard without a real data sample but none of the 2 last measures give me what I'm expected.

 

Maybe that's gonna help.

 

What I want is to divide the number of "association" with a payment by the number of "valid association"

 

Here my 2 measure for that

 

avec Paiement = 
CALCULATE (
    COUNTROWS ( Associations ),
    FILTER(
        Associations,
        Associations[first_payment_date] <> BLANK ()
    ),
    FILTER (
        Associations,
        Associations[state] = "Publiee"
            || Associations[state] = "GrosseAssociation"
            || Associations[state] = "AttentePublication"
    )
)

----

Inscrites Valides = 
CALCULATE (
    COUNTROWS ( Associations ),
    FILTER (
        Associations,
        Associations[state] = "Publiee"
            || Associations[state] = "GrosseAssociation"
            || Associations[state] = "AttentePublication"
    )
)

When I add a simple "DIVIDE()" of those 2 and I add it as a card I got the correct answer but if I had this measure into the matrix I got 100%.

 

I also tried w/o success something like :

 

MonthPercentage = DIVIDE(
    Associations[avec Paiement],
    CALCULATE(
        Associations[Inscrites Valides],
        FILTER ( 'Date', 'Date'[Date] >= MAX ( 'Date'[Date] ) && 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    ),
    0)

 EDIT :
Looking around I think this is something like this that I want to acheive :
https://community.powerbi.com/t5/Desktop/How-to-create-a-calculated-row-in-a-matrix/td-p/42239