Creating a measure based on rows that meet a certain criteria

[dhummel]

Hi all - I'm looking for a specific layout for my Matrix, like the below

                January                        |                   February

  Contracts      |      Proposals       |        Contracts     |       Proposals

open | closed  |   open  |  closed  |     open   closed  |    open   |   closed

My tables look like such - it holds either all open or all closed, currently I have 2 of them.

Columns: customer, month, metric type, value

                  abc123      1         contracts     12

                  joes            1        proposals     5

                  abc123      1        proposals     24

                  joes            1        contracts     1

What I want to do is create Measure that takes (for each customer, month, and metric type) the contracts and divides them by the proposals to give me a percent of proposals that make it to contracts. The logic is similar to what I'm actually doing - just simplified. I have done Measure on whole columns before but never row based on certain criteria. Can this even be done?

[mahoneypat]

I think this is what you are looking for, but please let me know if not.  I put your data in a table called 'Data'.  This measure summarizes contracts and proposals for each customer/month in the current context and then iterates through them all to give you an overall percentage of contracts/proposals.  It would work in a table visual with Customer and Month columns, or it would work in a card or other visual.

Pct Contracts = var summarytable = ADDCOLUMNS(SUMMARIZE(Data, Data[Customer], Data[Month]), "Proposals", CALCULATE(SUM(Data[Value]), Data[Metric Type]="proposals"), "Contracts", CALCULATE(SUM(Data[Value]), Data[Metric Type]="Contracts"))

var result = SUMX(summarytable, [Contracts]/[Proposals])

return result

Also, I would suggest, if your two tables are highly similar ,that you append them into one table with an extra column to show open/closed status.

If this works for you, please mark it as the solution.  Kudos are also appreciated.  Please let me know if not.
Regards,
Pat

[dhummel]

Hi Pat. I actually am not looking for 1 overall percentage. I need 1 percentage rate for each customer per month. Then this percentage rate can be graphed visually, or, as I intend to do.... add it to my matrix as another column next to open and closed.

[mahoneypat]

Yes.  This measure will work in a table too (or a line chart). I assume you would make a table with Customer and Month like in your original post, but I wrote it so it could be used other ways too.  Have you tried it in a table?  I tried it with your sample data, and it seems to be what you were describing.

Is that what you were looking for?

Regards,

Pat

[dhummel]

Ok so it seems like this is working in a table, but not a matrix - is there a reason why?

[dhummel]

I am receiving NANs, no numbers at all. Is there a way to see the summarytable var?