cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
donghoonkim1226 Frequent Visitor
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

Accepted Solutions
Highlighted
vega Member
Member

Re: Need help with custom DAX or Quick Measure

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.

3 REPLIES 3
vega Member
Member

Re: Need help with custom DAX or Quick Measure

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

donghoonkim1226 Frequent Visitor
Frequent Visitor

Re: Need help with custom DAX or Quick Measure

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

Highlighted
vega Member
Member

Re: Need help with custom DAX or Quick Measure

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.