Forum Discussion
Matrix - Calculation inside a matrix
- Anonymous7 years ago
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
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 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.
- Anonymous7 years agoNot 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
- Anonymous7 years agoNot 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 - Anonymous7 years agoNot 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 :
- Anonymous7 years agoNot applicable
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