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

Anonymous It's weird I just posted an answer but I cannot find it in the thread...

 

Ok so sorry but I understand what you are trying to do but it doesn't work here.

 

I try to dig deeper to understand why a simple division of two differents values gives me 100% in my matrix.

 

So I added in a matrix the 2 calculations I have made as a value

 

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

There is now way that both can have the same value at the end since I'm adding a second FILTER to the first measure.

 

But this is what I got :

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous

    Today I have access to Power BI it will be much easier :smileywink:

    Let start again. Assuming the table on the left is your data, the table on the right isnt the expected output (we can display percents instead of 0.xx) ? And if not could you show me what the expected result ? I'm confused aha but i'm sure we can get you what you want

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