Forum Discussion
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.
[Message too big I will post as a response the next...]
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.
12 Replies
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 measureLet me know if it works !Regards, EtienneBy the way we can speak in french if you want ;)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.
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
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.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.2019Mois d'inscription de l'association Janvier Février Mars Avril Mai Juin Juillet août sept. oct. nov. déc. Assos inscrites 2988 2467 3637 3988 3880 3243 141 Assos actives1314 1122 1323 1156 1025 488 3 44% 45% 36% 29% 26% 15% Assos inactives 1674 1345 2314 2832 2855 2755 Assos qui s'activent entre : J+0 - J+14669 532 730 647 684 292 22% 20% 20% 16% 18% 9% J+15 - J+30257 247 251 252 255 8 9% 10% 7% 6% 7% 0% J+31 - J+45133 97 133 133 76 4% 4% 4% 3% 2% J+46 et +255 246 209 124 0 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.