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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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