I am trying to build a visual to show payment progress for contracts but am having some difficulty having all the items show up in my visual when I use a slicer to filter the data. I am trying to be able to slice previous payments to have a historical view of payment progress.
In my data table not even line appears on every payment and it doesn't get recorded as a 0 either so when I filter to a payment the visual will only display the rows that have that associated payment number in my data table.
The pbix file can be found here: https://www.dropbox.com/scl/fi/3wpe3w85fxvjsefhphs4p/Contract-Data.pbix?rlkey=6qbi25w0xge8wh6bl2i5wmvsk&st=clyp5prn&dl=0
I have tried to create a measure that ignores the filter, creating a measure to look at all rows and pull the last known value if it is not in the selected payment in the slicer, selecting the show values with no data, and creating a seperate table with all line guids.
The raw data I am using:
Contract | Line Number | Line GUID | Pay Amount | Payment Number |
---|---|---|---|---|
1 | 1 | AAA1 | 10 | 1 |
1 | 1 | AAA1 | 500 | 2 |
1 | 1 | AAA1 | 1000 | 5 |
1 | 2 | BBB2 | 500 | 4 |
1 | 3 | CCC3 | 500 | 2 |
1 | 3 | CCC3 | 100 | 3 |
1 | 3 | CCC3 | 100 | 7 |
1 | 4 | DDD4 | 100 | 6 |
1 | 5 | EEE5 | 500 | 3 |
2 | 1 | 111A | 100 | 1 |
2 | 1 | 111A | 500 | 3 |
2 | 2 | 222B | 1000 | 3 |
2 | 2 | 222B | 500 | 3 |
2 | 3 | 333C | 500 | 2 |
2 | 3 | 333C | 1000 | 3 |
you can create two tables for selection
contract = DISTINCT('Table'[Contract])
paymentnumber = DISTINCT('Table'[Payment Number])
Do not create relationships between tables.
Then create mesaures
MEASURE =
CALCULATE (
SUM ( 'Table'[Pay Amount] ),
FILTER (
'Table',
'Table'[Contract] = MAX ( contract[Contract] )
&& 'Table'[Payment Number] = MAX ( paymentnumber[Payment Number] )
)
) + 0
MEASURE 2=
CALCULATE (
SUM ( 'Table'[Pay Amount] ),
FILTER (
'Table',
'Table'[Contract] = MAX ( 'contract'[Contract] )
&& 'Table'[Payment Number] < MAX ( paymentnumber[Payment Number] )
)
) + 0