Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |