Reply
Frequent Visitor
Posts: 9
Registered: ‎10-04-2017
Accepted Solution

Need help with custom DAX or Quick Measure

[ Edited ]

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.


Accepted Solutions
Highlighted
Member
Posts: 100
Registered: ‎09-14-2017

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.

View solution in original post


All Replies
Member
Posts: 100
Registered: ‎09-14-2017

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.

Frequent Visitor
Posts: 9
Registered: ‎10-04-2017

Re: Need help with custom DAX or Quick Measure

[ Edited ]

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
Member
Posts: 100
Registered: ‎09-14-2017

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.