- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Regards,
Pat
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok so it seems like this is working in a table, but not a matrix - is there a reason why?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am receiving NANs, no numbers at all. Is there a way to see the summarytable var?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
09-17-2023 10:45 AM | |||
08-05-2024 10:02 PM | |||
07-11-2024 03:26 PM | |||
03-14-2024 04:55 AM | |||
01-09-2023 12:09 PM |
User | Count |
---|---|
14 | |
14 | |
11 | |
11 | |
8 |