Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dhummel
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
mahoneypat
Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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.

 

contracts.png

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


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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors