Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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. 

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi @Anonymous 

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 😉 
Anonymous
Not applicable

Merci beaucoup Etienne @Anonymous 

 

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.

Anonymous
Not applicable

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
Not applicable

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

 

matrix_2measures.png

 

 

 

 

Anonymous
Not applicable

Hi @Anonymous

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

Anonymous
Not applicable

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


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"

Anonymous
Not applicable

@Anonymous 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 ! 

 

Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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
Not applicable

Here a sample of my dahboard @Anonymous .

Hope I didn't mess up 😉

test.pbix : https://framadrop.org/r/4DAOiaa5t8#tx2oZYocF3TR1PDsz1ID4XvEGzY4oq/z4/aArQLuLTo=

 

On top you have the "classic" matrix and bottom is where I want the % you are trying to help me with.
Left is about campaign, right is about payment.

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.