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...]
Solved! Go to Solution.
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.
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] )
2019 | Mois 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 actives | 1314 | 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+14 | 669 | 532 | 730 | 647 | 684 | 292 | ||||||
22% | 20% | 20% | 16% | 18% | 9% | |||||||
J+15 - J+30 | 257 | 247 | 251 | 252 | 255 | 8 | ||||||
9% | 10% | 7% | 6% | 7% | 0% | |||||||
J+31 - J+45 | 133 | 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.
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)
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.
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
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
@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 :
Hi @gilHA
Today I have access to Power BI it will be much easier
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
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".
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"
@gilHA indeed we're really close
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 !
User | Count |
---|---|
89 | |
75 | |
65 | |
49 | |
47 |