Forum Discussion
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:
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.
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:
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.