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

We are closer and closer ! Anonymous 


The ratio I want is not current_value / row_total (so with a 100% at the end) but current_value/valid_total.

Look at this Venn like diagram.
I have my table associations.

I made a measure where I get what we call the "Valid Association"
Then I made another measure from the full table Association where I get only the one with a value (date) on my column "first_payment_date".


 

The column "Groupe répartition activation" is a calculated column where I get the number of day between the subscription date and the date of the first payment.


Here another screenshot with the raw values : The % I want in this case is 1003/1943 = 51,62% for "Jan 2018 - J+0"

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous indeed we're really close :smileyhappy:

 

Check this out : 

As you can see : 

For january 2018 : 1/8=12.5%

For total : 5/22=22.7%

 

Here is the formula i used 

MonthPercentage = DIVIDE(
 CALCULATE([Avec Campagnes]),
 CALCULATE([Inscrites Valides], ALLEXCEPT(Associations,Associations[date_inscription].[Year],Associations[date_inscription].[Month])),0)

Your problem here was to divide : the value of "Avec Campagnes" by the Value of "Inscrites Valides" for the current Year/Month.

I hope i'm right this time ! 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    Anonymous 

    So I already try something similar but I don't have the same result as you.

     

    First I cannot have .[Year] or .[Month] on Association[date_inscription] like you do without an error :

     

    Column reference to 'date_inscription' in table 'Associations' cannot be used with a variation 'Year' because it does not have any.

     

    So I had do do something like :

    MonthPercentage = DIVIDE(
 CALCULATE([Avec Campagnes]),
 CALCULATE([Inscrites Valides], ALLEXCEPT(Associations,Associations[date_inscription])),0)

    Also I don't have a calculated columns for "Inscrites Valides" and "avec Campagnes" like you did in your exemple.

    It's a measure for both, I'm not sure if that change anything ? It's just a measure that check if there is a certain values inside Associations[state] for "Valides" and a not a BLANK() in Associations[first_campagnes_date] for "avec Campagnes".