topic Re: Creating a measure based on rows that meet a certain criteria in DAX Commands and Tips

Creating a measure based on rows that meet a certain criteria

dhummel — Mon, 04 May 2020 21:59:54 GMT

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

January | February

Contracts | Proposals | Contracts | Proposals

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?

Re: Creating a measure based on rows that meet a certain criteria

mahoneypat — Mon, 04 May 2020 22:38:27 GMT

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

Re: Creating a measure based on rows that meet a certain criteria

dhummel — Mon, 04 May 2020 22:42:15 GMT

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.

Re: Creating a measure based on rows that meet a certain criteria

mahoneypat — Mon, 04 May 2020 23:03:37 GMT

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

Re: Creating a measure based on rows that meet a certain criteria

dhummel — Tue, 05 May 2020 12:53:35 GMT

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

Re: Creating a measure based on rows that meet a certain criteria

dhummel — Tue, 05 May 2020 13:06:43 GMT

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