cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Creating a measure based on rows that meet a certain criteria

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?

5 REPLIES 5
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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.

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors