cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gilHA Regular Visitor
Regular Visitor

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_date]) and I have a calculated column of the delta time between the subscription date and the first payment (User[first_pay]). From this last calculated column I made another column to score them depending of my marketing team needs as differents group (Who get the first payment between 1 to 14 days after the subscription as "J+1 J+14" - between D+15 and D+30 as "J+15 J+30"... and so on).

 

The table look like this (column names is real here) :

 

id

date_inscription

first_payment_date

Groupe répartition activation

73486

01 January 2018

04 January 2018

J+1 - J+14

73487

01 January 2018

17 February 2019

J+46 et +

73488

01 January 2018

18 August 2018

J+46 et +

73496

01 January 2018

15 June 2018

J+46 et +

73497

01 January 2018

05 July 2018

J+46 et +

73500

01 January 2018

02 January 2018

J+1 - J+14

73502

01 January 2018

06 January 2018

J+1 - J+14

73504

01 January 2018

31 January 2018

J+15 - J+30

 

With that I can easily make a matrix of group value as column, month number as a row and the number of User with at least one payment that look like this.

matrix_activation.png

 

[Message too big I will post as a response the next...]

1 ACCEPTED SOLUTION

Accepted Solutions
EtienneOL Member
Member

Re: Matrix - Get as a value the ration of the value on a certain count

Hi @gilHA ,

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. 

12 REPLIES 12
gilHA Regular Visitor
Regular Visitor

Re: Matrix - Get as a value the ration of the value on a certain count

Now I would like to have the same matrix but the value should be the number I got in a cell, divided by the total number of "valid User" (all user but with some filter, measure that I already made). I though about doing a simple measure like and put it as the value of the matrix :

 
Ratio actives / valides = DIVIDE(
    User[with Paiement],
    User[Valide]
)
 
But I just get 100% everywhere.
bad_matrix.png
 
But If I put this measure as it is as a simply card I got the right number I was wainting for the whole set of data (I have a date slicer on this page).
 
A colleague did what we are trying to do with excel.
 
2019
Mois d'inscription de l'association
JanvierFévrierMarsAvrilMaiJuinJuilletaoûtsept.oct.nov.déc.
Assos inscrites298824673637398838803243141     
Assos actives
131411221323115610254883     
 44%45%36%29%26%15%     
Assos inactives167413452314283228552755      
             
Assos qui s'activent entre :            
J+0 - J+14
669532730647684292      
 22%20%20%16%18%9%     
J+15 - J+30
2572472512522558      
 9%10%7%6%7%0%     
J+31 - J+45
1339713313376       
 4%4%4%3%2%      
J+46 et +
2552462091240       
 9%10%6%3%0%      


The % (what I'm trying to get in my matrix) is the division of the number just upper by the total of User sub you can read on each columns.

 

For Febuary (Fevrier) J0 -J14 is 22% because it's 669/2988.

 

Hope is clear enough.

EtienneOL Member
Member

Re: Matrix - Get as a value the ration of the value on a certain count

Hi @gilHA 

The value displayed in your matrix is a count ? 

If yes create this measure 

Otherwise replace "Count('Table'[id])" by "SUM(anything you sum)"

MonthPercentage = DIVIDE(COUNT('Table'[id]),CALCULATE(COUNT('Table'[id]),'Table'[date_inscription]);0)
And then instead of displaying the count display the MonthPercentage measure
Let me know if it works ! 
Regards, Etienne
By the way we can speak in french if you want Smiley Wink 
gilHA Regular Visitor
Regular Visitor

Re: Matrix - Get as a value the ration of the value on a certain count

Merci beaucoup Etienne @EtienneOL 

 

I did a measure like this :

 

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

But I got the value of 1... so 100%.

I'm not sur I have explained well my dilemma. If someone need more details about it, tell me please.

EtienneOL Member
Member

Re: Matrix - Get as a value the ration of the value on a certain count

Oops sorry @gilHA 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

gilHA Regular Visitor
Regular Visitor

Re: Matrix - Get as a value the ration of the value on a certain count

Thank you @EtienneOL 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

gilHA Regular Visitor
Regular Visitor

Re: Matrix - Get as a value the ration of the value on a certain count

@EtienneOL 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 :

 

matrix_2measures.png

 

 

 

 

EtienneOL Member
Member

Re: Matrix - Get as a value the ration of the value on a certain count

Hi @gilHA

Today I have access to Power BI it will be much easier Smiley Wink

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

image.png

gilHA Regular Visitor
Regular Visitor

Re: Matrix - Get as a value the ration of the value on a certain count

We are closer and closer ! @EtienneOL 


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


venn.png

 

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 : matrixWValue.pngThe % I want in this case is 1003/1943 = 51,62% for "Jan 2018 - J+0"

EtienneOL Member
Member

Re: Matrix - Get as a value the ration of the value on a certain count

@gilHA indeed we're really close Smiley Happy

 

Check this out : 

image.png

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 ! 

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 69 members 985 guests
Please welcome our newest community members: