cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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...]

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Matrix - Get as a value the ration of the value on a certain count

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.

12 REPLIES 12
Regular Visitor

## Re: Matrix - Get as a value the ration of the value on a certain count

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.

 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.

Member

## Re: Matrix - Get as a value the ration of the value on a certain count

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)`
And then instead of displaying the count display the MonthPercentage measure
Let me know if it works !
Regards, Etienne
By the way we can speak in french if you want
Regular Visitor

## Re: Matrix - Get as a value the ration of the value on a certain count

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.

Member

## Re: Matrix - Get as a value the ration of the value on a certain count

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

Regular Visitor

## Re: Matrix - Get as a value the ration of the value on a certain count

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

Regular Visitor

## Re: Matrix - Get as a value the ration of the value on a certain count

@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 :

Member

## Re: Matrix - Get as a value the ration of the value on a certain count

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

Regular Visitor

## Re: Matrix - Get as a value the ration of the value on a certain count

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"

Member

## Re: Matrix - Get as a value the ration of the value on a certain count

@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 !

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 69 members 985 guests
Recent signins: