Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Help please - I'm new!
I want to write a report that shows for a selected date range (say December) the number of quotations issued and number of quotes won in the month.
For example I have the following data set
Effectively I want to calculate the quoted qty with reference to the [Quote date] and ignore the [Date won or lost] and calculate the Won or Lost quantity by referencing the [Date Won or Lost] but ignore the Quote Date
Any assistance would be greatly appreciated.
Solved! Go to Solution.
Does this layout work?
1.To get this one, go to Edit Queries -> Right click on your Table and duplicate it -> Let's name the tables as Table1 and Table2
2. Delete Date Won or Lost and Status columns from Table 1
3. Delete Quote Date column in Table 2
4. Click on Close&Apply to return to the Report View
5. Create a Date table and establish the connection with Table1 and Table2
Date = CALENDARAUTO()
6. Create 3 measures to calculate Qty Quoted, Qty Won and Qty Lost as below
Qty Quoted = SUMX( KEEPFILTERS(VALUES('Table1'[Quote Date])), CALCULATE(SUM('Table1'[Qty])) )
Qty Won = CALCULATE(SUM('Table2'[Qty]), Table2[Status]="W", KEEPFILTERS(Table2[Date Won or Lost]))
Qty Lost = CALCULATE(SUM('Table2'[Qty]), Table2[Status]="L", KEEPFILTERS(Table2[Date Won or Lost]))
7. Display these measures in a Matrix layout as Values and Date column from newly created Date table as Rows with hierarchy as Year and Month
Hi @KarlNixon,
Please download another solution from the attachment.
1. Create a date table.
Calendar = CALENDARAUTO()
2. Establish two relationships while only one is active.
3. Create three measures.
QTY Quoted = sum(Table1[QTY])
QTY Won = CALCULATE ( SUM ( Table1[QTY] ), USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ), Table1[Status] = "W" )
QTY Lost = CALCULATE ( SUM ( Table1[QTY] ), USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ), Table1[Status] = "L" )
Best Regards,
Hi @KarlNixon,
Please download another solution from the attachment.
1. Create a date table.
Calendar = CALENDARAUTO()
2. Establish two relationships while only one is active.
3. Create three measures.
QTY Quoted = sum(Table1[QTY])
QTY Won = CALCULATE ( SUM ( Table1[QTY] ), USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ), Table1[Status] = "W" )
QTY Lost = CALCULATE ( SUM ( Table1[QTY] ), USERELATIONSHIP ( 'Calendar'[Date], Table1[Date Won or Lost] ), Table1[Status] = "L" )
Best Regards,
Thank you - that is the perfect solution!
Much appreciated!!!!
Does this layout work?
1.To get this one, go to Edit Queries -> Right click on your Table and duplicate it -> Let's name the tables as Table1 and Table2
2. Delete Date Won or Lost and Status columns from Table 1
3. Delete Quote Date column in Table 2
4. Click on Close&Apply to return to the Report View
5. Create a Date table and establish the connection with Table1 and Table2
Date = CALENDARAUTO()
6. Create 3 measures to calculate Qty Quoted, Qty Won and Qty Lost as below
Qty Quoted = SUMX( KEEPFILTERS(VALUES('Table1'[Quote Date])), CALCULATE(SUM('Table1'[Qty])) )
Qty Won = CALCULATE(SUM('Table2'[Qty]), Table2[Status]="W", KEEPFILTERS(Table2[Date Won or Lost]))
Qty Lost = CALCULATE(SUM('Table2'[Qty]), Table2[Status]="L", KEEPFILTERS(Table2[Date Won or Lost]))
7. Display these measures in a Matrix layout as Values and Date column from newly created Date table as Rows with hierarchy as Year and Month
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |