Forum Discussion
Compare, evaluate, and visualize “mismatched” data across a table
Hello!
I have been trying for weeks to figure out the best way to determine then visualize the occurrence of what I call mismatched data within a table.
consider my sample data below in the following layout:
Date | Member | Vendor | SKU | Category |
9/22/22 | A | Homemade | 11-110 | Price Match |
9/20/22 | A | Homemade | 11-110 | No Price Found |
9/12/22 | B | Wooden | 23-1111 | Price Match |
8/20/22 | C | Homemade | 000-245 | Price Match |
8/15/22 | B | Wooden | 23-1111 | No Price Found |
7/10/22 | B | Wooden | 23-1111 | No Price Found |
7/02/22 | A | Homemade | 11-110 | Price Match |
7/01/22 | C | Homemade | 000-245 | Price Match |
I am trying to create a measure or configure the table above so that for each Member, Vendor, and SKU, the resulting category is compared to previous categories of the same Member, Vendor, and SKU.
For example, Member A purchased item 11-110 from the same vendor on 3 different occasions. 2 of those occasions resulted in a category of Price Match but 1 of those purchases resulted in a category of No Price Found. I would like the purchase that resulted in the "mismatch" to be displayed in the table or visual.
Doing this will help me track, prioritize work, and highlight category mismatches that occur.
I appreciate any help!
PortoIvan Did you mean that for B that the mismatch is "Price Found"? If that is the case, then you could do this:
Complex Selector = VAR __Member = MAX('Table'[Member ]) VAR __Category = MAX('Table'[Category ]) VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Member ] = __Member),[Category ],"__Count",COUNTROWS('Table')) VAR __Min = MINX(__Table,[__Count]) VAR __MinCategory = MINX(FILTER(__Table, [__Count] = __Min),[Category ]) RETURN IF(__Category = __MinCategory,1,0)
6 Replies
- Greg_Deckler
Community Champion
PortoIvan Couldn't you just use the Filters pane to remove any "Price Match" for Category?
Greg_Deckler Unfortunately no, because although that would work for one member for another member a category of "Price Match" might not be the "mismatch". For example using the same table I provided Member B ordered Item 23-111 multiple times, however in their case the mismatch is the 1 purchase they made where the item had a category of Price Match, since No Price Found occurred twice where as Price Match occurred once.
- Greg_Deckler
PortoIvan Did you mean that for B that the mismatch is "Price Found"? If that is the case, then you could do this:
Complex Selector = VAR __Member = MAX('Table'[Member ]) VAR __Category = MAX('Table'[Category ]) VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Member ] = __Member),[Category ],"__Count",COUNTROWS('Table')) VAR __Min = MINX(__Table,[__Count]) VAR __MinCategory = MINX(FILTER(__Table, [__Count] = __Min),[Category ]) RETURN IF(__Category = __MinCategory,1,0)
You're amazing! Thank you! This solved my problem!!
