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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
donghoonkim1226
Frequent Visitor

Need help with custom DAX or Quick Measure

I am building a report for all the quotes that are going out at my company. 

I have built 99% of the report but I am having some trouble creating a custom summary of the quote value.

 

For example:

 

You have 1 Quote ID that can be sent to multiple customers.

Quote ID: 10001 - Customer A - Quote Value: $1000

Quote ID: 10001 - Customer B - Quote Value: $1000

Quote ID: 10001 - Customer C - Quote Value: $1000

Quote ID: 10001 - Customer D - Quote Value: $1000

Quote ID: 10001 - Customer E - Quote Value: $1000

 

When reporting on Quote ID: 10001 - the total  Quote Value should be $1000, not $5000.

 

I am new to Power BI and I have been having trouble finding a solution to this.


Can anyone help me or lead me in the right direction?

 

I have tried looking into the quick mesures to see if anything could be done but I am unable to replicate the outcome I need.

 

Thank you in advance for your help.

1 ACCEPTED SOLUTION

This one is a tricky one. I don't have a clean solution that can be done with one DAX expression, unfortunately.  A solution to this problem would be to create a calculated table using the following formula:

 

Table = DISTINCT(Table2[Quote ID])

This would create a dimension table. You can then create a relationship between your dimension table and the fact table via the Quote ID columns. From there, you can add a calculated column that retrieves the Quote ID value. You can do this with:

 

Quote Value = CALCULATE(MAX(Table2[Value]))

That should give you the result you need:

 

Capture.PNG

 

I can't figure out how to do it without creating a dimension table. If anyone else can figure out how to incorporate all of this without a dimension table, please chime in.

View solution in original post

3 REPLIES 3
vega
Resolver III
Resolver III

If the Quote Value never changes from the Quote ID then you can just use MAX instead of SUM the values.

I should have been a little more specific in the details. Please view below for further explanation.

 

Quote ID: 10001 - Customer A - Quote Value: $1000

Quote ID: 10001 - Customer B - Quote Value: $1000

Quote ID: 10001 - Customer C - Quote Value: $1000

Quote ID: 10001 - Customer D - Quote Value: $1000

Quote ID: 10001 - Customer E - Quote Value: $1000

 

Quote ID:  10002 - Customer AB - Quote Value: $3000

 

Quote ID:  10003 - Customer AC - Quote Value: $4000

 

Power BI is reporting the Total Quote Value as $12,000.00

When the Total Quote Value should be $8000.00

This one is a tricky one. I don't have a clean solution that can be done with one DAX expression, unfortunately.  A solution to this problem would be to create a calculated table using the following formula:

 

Table = DISTINCT(Table2[Quote ID])

This would create a dimension table. You can then create a relationship between your dimension table and the fact table via the Quote ID columns. From there, you can add a calculated column that retrieves the Quote ID value. You can do this with:

 

Quote Value = CALCULATE(MAX(Table2[Value]))

That should give you the result you need:

 

Capture.PNG

 

I can't figure out how to do it without creating a dimension table. If anyone else can figure out how to incorporate all of this without a dimension table, please chime in.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors