Forum Discussion
Matrix - Calculation inside a matrix
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.
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
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 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 !
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".
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.