cancel
Showing results 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

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.

1 ACCEPTED SOLUTION
Resolver III

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:

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.

3 REPLIES 3
Resolver III

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

Frequent Visitor

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

Resolver III

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:

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors