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