False Tie in RankX - but only when I select Customer 5!

[WilliamPHII]

I tried adding a basic card to my Power BI file, and what should have taken 5 minutes has stretched to an hour and half.

I have one table. It has maybe 150 unique customers and 12,000 rows with all their invoices.

I made a simple RankX measure: RankX(All(Table[CustomerName]),[Total Sales]). Total sales is a basic "Sum(Table[Invoices]).

When I drop this RankX measure in a table on Power BI, it works like a charm. I add Total Sales, and I can see it is working perfectly.

The problem is when I slice the table for one customer, using the same customer column in the slicer! When I do that, and select for one customer, all is well for Customer ranked 1, 2, 3, and 4. But when I click on the slicer on the customer I know is ranked 5, the table shows it as 6. When I slice for the customer I know is 6, it ALSO shows six. The total sales measure in the same table still plainly shows them as having different total sales. When I move on to Customer 7, 8, and so on, the table shows the correct value. It seems something is seriously wrong with customer 5. And I have no idea what it could be.

I have no blanks in my customer column or invoice column.

In my real model I have the customers as a lookup table. But the same thing was happening using a lookup table, and once I realized it happened when I just used one table, I figured it be easiest to keep relationships out of it and focus on the case of just one table.

Very grateful for anyone that can help or even toss a crumb of an idea my way.

William

 

[WilliamPHII]

Correction: I see it happens for other customers down the line as well. Weird ties that are absolutely false.

I'm just not good enough to understand how this could happen only when I slice for one customer. And it doesn't happen for every customer--just here and there as you go up down the ranking.

 

[JustynaMK]

Hi William,

I tried to recreate your issue but without much success - I get correct results for all my 10 exemplary clients. I have used the same measures as you did.

Have you tried adding [Total Sales] to your main table and to the "filtered" table and see if the value is also changing? Alternatively, is there any chance you could share your underlying data or the .pbix file itself?

 

[WilliamPHII]

Well, the latest twist is I went to get a clean file for you with fake customers names and only two columns (Customer and Invoice) and I discovered when I import only those two columns from my Excel file, the bug doesn't happen!

Any thoughts on that?? I need the whole table for my report, but apparently something is throwing if off...

 

[WilliamPHII]

Ok, I have a clean file I can give you, either in Excel or PowerBI. How do I get you a file?

 

[WilliamPHII]

Hmmm. I'm trying to the XL2BB, but not sure it will take 12,000 rows, or that I can figure it out...

 

[WilliamPHII]

SampleFile2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Customer	Invoice	Estimated Labor Hours	Estimated Labor Cost	Estimated Material Cost	Estimated Other Costs	Estimated Total Cost	Actual Labor Hours	Actual Labor Cost	Actual Material Cost	Actual Other Costs	Actual Total Cost	Profit Amount	Profit %
2	80B	10240	3300	1782	3078.65	550	5410.65	2970	1832.49	3069.02065	755.425	5656.93565	4583.06435	0.4476
3	64L	214.76	0	0	0	0	0	0	0	163.84	0	163.84	50.92	0.2371
4	17Q	39835.5	14661.48	7917.21	17807.38	720	26444.59	15087	9243.054	16963.87993	1354.2	27561.13393	12274.36607	0.3081
5	17Q	39835.5	9007.73	4864.18	11092.92	720	16677.1	10462	6441.704	9653.5102	381.825	16477.0392	23358.4608	0.5864
6	17Q	0	14661.48	7917.21	17807.38	720	26444.59	0	0	0	0	0	0	0
7	17Q	0	0	0	0	0	0	0	0	0	0	0	0	0
8	23W	5642	1812.77	978.91	2802.09	150	3931	1582	976.094	2535.99582	272.3993515	3784.489172	1857.510828	0.3292
9	23W	2618	817.22	441.29	1253.66	150	1844.95	582	359.094	1085.34499	100	1544.43899	1073.56101	0.4101
10	23W	6934	2080.79	1123.66	3405.07	150	4678.73	1908	1164.711	3029.13513	114.8604236	4308.706554	2625.293446	0.3786
11	71S	1628	359.14	193.93	698.48	380	1272.41	329	202.993	589.19846	132.2945768	924.4860368	703.5139632	0.4321
12	36J	1180	253.7	136.99	415.71	380	932.7	232	143.144	377.22838	121.523675	641.896055	538.103945	0.456
13	16P	6020	1832.37	989.52	2740.31	380	4109.83	1636	1009.412	2408.43597	393.8646631	3811.712633	2208.287367	0.3668
14	16P	4850.08	0	0	0	0	0	0	0	3095.53	380.3027697	3475.83277	1374.24723	0.2833
15	43Q	5975	1658.04	895.38	2301.95	550	3747.33	1413	871.821	2455.35461	504.5153239	3831.690934	2143.309066	0.3587
16	35I	8288	2423.21	1308.56	3102.77	720	5131.33	2150	1326.55	2859.93059	844.225	5030.70559	3257.29441	0.393
17	44R	6974	1813.41	979.3	2680.92	380	4040.22	2272	1401.824	2361.35983	361.8652284	4125.049058	2848.950942	0.4085
18	44R	0	0	0	0	0	0	0	0	0	0	0	0	0
19	23W	2037	613.14	331.09	878.43	150	1359.52	653	402.901	781.32211	100	1284.22311	752.77689	0.3696
20	15O	17617	4519.98	2440.84	6882.33	1440	10763.17	4582	2827.094	6812.90727	1394.465	11034.46627	6582.53373	0.3736
21	45S	2754	615.8	332.53	1192.31	550	2074.84	368	227.056	976.21095	173.441375	1376.708325	1377.291675	0.5001
22	56D	664	98.42	53.15	176.2	275	504.35	70	43.19	151.81079	100	295.00079	368.99921	0.5557
23	150T	150	0	0	0	0	0	0	0	0	0	0	150	1
24	17Q	300	0	0	0	0	0	0	0	0	0	0	300	1
25	61I	1023.88	0	0	0	0	0	0	0	0	0	0	1023.88	1
26	26Z	668.64	0	0	0	0	0	0	0	503.8	0	503.8	164.84	0.2465
27	7G	1274	267.32	144.35	442.06	380	966.41	210	129.57	365.55509	158.6727045	653.7977945	620.2022055	0.4868
28	43Q	2775	566.8	306.07	1205.98	550	2062.05	674	415.858	1098.72461	234.3146483	1748.897258	1026.102742	0.3698
29	79A	9898	1611.15	870.02	5043.68	720	6633.7	2027	1231.384	4333.5876	876.35	6441.3216	3456.6784	0.3492
30	64L	100.2	0	0	0	0	0	0	0	74.69	0	74.69	25.51	0.2546
31	125U	100	0	0	0	0	0	0	0	0	0	0	100	1
32	7G	3851	845.32	456.47	1444.67	632	2533.14	820	505.94	1335.50337	845.025	2686.46837	1164.53163	0.3024
33	16P	56	0	0	0	0	0	0	0	41.64	0	41.64	14.36	0.2564
34	149S	2635.2	0	0	0	0	0	0	0	1943.21	165.9595902	2109.16959	526.0304098	0.1996
35	159C	0	0	0	0	0	0	0	0	0	0	0	0	0
36	2B	1432	300.91	162.49	562.76	380	1105.25	271	167.207	509.66998	111.3036652	788.1806452	643.8193548	0.4496
37	16P	1140	233.38	126.02	452.99	380	959.01	215	132.655	407.81514	113.3463733	653.8165133	486.1834867	0.4265
38	45S	193	23.67	12.79	52.56	150	215.35	72	44.424	45.57297	100	189.99697	3.00303	0.0156
39	64L	3810	1054.02	569.16	1953.28	380	2902.44	730	450.41	1695.34121	183.9492253	2329.700435	1480.299565	0.3885
40	18R	346.08	0	0	0	0	0	0	0	250.93	100	350.93	-4.85	-0.014
41	45S	1569	238.21	128.63	630.66	275	1034.29	248	153.016	569.53969	133.9017794	856.4574694	712.5425306	0.4541
42	21U	600	111.03	59.96	173.16	190	423.12	140	86.38	150.21282	100	336.59282	263.40718	0.439
43	109E	1159	233.46	126.07	470.4	190	786.47	207	127.719	408.06495	100	635.78395	523.21605	0.4514
44	97S	7719	2246.99	1213.39	3011.22	550	4774.61	1918	1183.406	2715.36919	291.9684641	4190.743654	3528.256346	0.4571
45	12L	4153	924.02	498.96	1495.9	720	2714.86	1057	632.969	1355.49565	751.1869333	2739.651583	1413.348417	0.3403
46	54B	1356	187.05	101.01	302.09	720	1123.1	161	99.337	270.24883	216.8962652	586.4820952	769.5179048	0.5675
47	54B	1376	293.61	158.52	367.63	360	886.15	344	212.248	343.55916	220.0953252	775.9024852	600.0975148	0.4361
48	1A	736	95.27	51.44	209	275	535.44	184	113.528	183.00287	100	396.53087	339.46913	0.4612
49	1A	1600	293.1	158.27	576.65	275	1009.92	191	117.847	509.57611	199.2749821	826.6980921	773.3019079	0.4833
50	32F	4181	712.55	384.78	2140.84	380	2905.62	972	599.724	1793.05955	263.3109618	2656.094512	1524.905488	0.3647
51	32F	2168	425.62	229.84	1168.32	380	1778.16	534	329.478	1007.16144	136.5362749	1473.175715	694.8242851	0.3205
52	8H	2118	521.93	281.84	867.41	550	1699.25	742	457.814	764.11658	100	1321.93058	796.06942	0.3759
53	71S	150	0	0	0	0	0	0	0	0	0	0	150	1
54	30D	476.68	0	0	0	0	0	0	0	355.06	0	355.06	121.62	0.2551
55	26Z	1696	368.93	199.22	532.68	380	1111.9	355	219.035	646.33463	104.9157704	970.2854004	725.7145996	0.4279
56	16P	7002	1739.03	939.09	2794.31	380	4113.4	1855	1144.535	2878.83902	264.848191	4288.222211	2713.777789	0.3876
57	16P	461.56	0	0	0	0	0	0	0	338.09	100	438.09	23.47	0.0508
58	23W	2697	804.97	434.68	1199.52	150	1784.2	856	528.152	1044.86762	100	1673.01962	1023.98038	0.3797
59	23W	4738	1510.97	815.92	2295.8	150	3261.72	1519	937.223	2067.1905	100	3104.4135	1633.5865	0.3448
60	16P	4344	1279.33	690.88	1915.98	380	2986.86	1022	630.574	1685.86479	272.9973509	2589.436141	1754.563859	0.4039
61	16P	3015	0	0	0	0	0	0	0	2054.86	100	2154.86	860.14	0.2853
62	109E	179	20.84	11.25	43.86	100	155.11	34	20.978	37.43363	100	158.41163	20.58837	0.115
63	8H	1894	312.96	169	752.7	275	1196.7	245	151.165	656.743	235.89176	1043.79976	850.20024	0.4489
64	20T	4390	1218.14	657.79	2090.87	150	2898.66	1071	646.257	1850.6198	100	2596.8768	1793.1232	0.4085
65	20T	4104	1210.92	653.92	2008.72	150	2812.64	940	579.98	1742.54416	100	2422.52416	1681.47584	0.4097
66	20T	3275	912.39	492.68	1530.62	150	2173.3	856	528.152	1353.85653	143.991877	2026.000407	1248.999593	0.3814
67	20T	4667	1468.23	792.85	2208.83	150	3151.68	1459	900.203	2007.02637	100	3007.22937	1659.77063	0.3556
68	20T	491	154.42	83.39	236.92	150	470.31	155	95.635	206.87465	100	402.50965	88.49035	0.1802
69	111G	3905	938.26	506.67	1603.71	720	2830.38	888	547.896	1397.31602	527.5581504	2472.77017	1432.22983	0.3668
70	27A	421.8	0	0	0	0	0	0	0	348.02	0	348.02	73.78	0.1749
71	15O	7114.5	2131.63	1151.11	2881.92	720	4753.03	1481	913.777	2617.37522	766.4108762	4297.563096	2816.936904	0.3959
72	12L	1339	263.27	142.17	367.48	720	1229.65	301	185.717	381.00118	491.5	1058.21818	280.78182	0.2097
73	69Q	239	64.73	34.96	140.46	0	175.42	170	104.89	126.98441	0	231.87441	7.12559	0.0298
74	105A	859	161.41	87.17	249.89	275	612.06	176	107.017	222.98526	100	430.00226	428.99774	0.4994
75	107C	450	0	0	0	0	0	0	0	0	0	0	450	1
76	17Q	0	2251.89	1216.02	2667.33	720	4603.35	1834	1131.578	2332.44872	175.4154913	3639.442211	-3639.442211	0
77	149S	1081	230.95	124.72	359.39	190	674.11	254	156.718	313.34315	100	570.06115	510.93885	0.4727
78	62J	165.2	0	0	0	0	0	0	0	118.98	100	218.98	-53.78	-0.3255
79	7G	3868	822.18	443.96	1629.54	550	2623.5	680	414.01	1481.14519	120.8421907	2015.997381	1852.002619	0.4788
80	20T	1212.96	0	0	0	0	0	0	0	784.04	100	884.04	328.92	0.2712
81	30D	203.84	0	0	0	0	0	0	0	144.86	0	144.86	58.98	0.2893
82	64L	6779	1978.4	1068.33	3250.6	380	4698.93	1407	868.119	2789.72296	298.0521937	3955.894154	2823.105846	0.4164
83	32F	150	0	0	0	0	0	0	0	0	0	0	150	1
84	77Y	2899.2	0	0	0	0	0	0	0	2016.52	0	2016.52	882.68	0.3045
85	18R	4876.48	1301.95	703.05	2401.24	380	3484.29	1100	678.7	2062.90176	214.4041247	2956.005885	1920.474115	0.3938
86	93O	2894	559.33	302.04	1324.33	550	2176.37	517	318.989	1184.7935	781.0895525	2284.872052	609.1279475	0.2105
87	95Q	1064	332.15	179.36	400.16	190	769.52	431	265.927	354.95381	100	720.88081	343.11919	0.3225
88	30D	1872	0	0	0	0	0	0	0	1489.33	0	1489.33	382.67	0.2044
89	18R	491.96	87.74	47.38	125.77	190	363.15	92	56.764	109.76479	100	266.52879	225.43121	0.4582
90	95Q	1421	292.07	157.72	575.68	380	1113.4	332	204.844	475.07124	100	779.91524	641.08476	0.4512
91	23W	340	79	42.66	118.7	150	311.36	141	86.997	114.50975	100	301.50675	38.49325	0.1132
92	20T	3783	1080.35	583.39	1835.07	150	2568.46	1012	624.404	1627.46874	109.9977687	2361.870509	1421.129491	0.3757
93	77Y	163.08	0	0	0	0	0	0	0	113.43	0	113.43	49.65	0.3045
94	23W	529.58	210.02	113.41	198.46	150	461.87	114	70.338	194.71575	100	365.05375	164.52625	0.3107
95	70R	145	25.75	13.9	36.72	150	200.62	31	19.127	33.079	100	152.206	-7.206	-0.0497
96	79A	1679	160.32	86.57	304.48	720	1111.05	137	84.529	267.01706	145.2864785	496.8325385	1182.167462	0.7041
97	20T	1246	408.33	220.5	512.7	150	883.2	403	248.651	460.03337	100	808.68437	437.31563	0.351
98	20T	2872	890.01	480.61	1372.34	150	2002.95	655	404.135	1189.36988	100	1693.50488	1178.49512	0.4103
99	143M	0	319.68	172.63	869.2	380	1421.83	278	171.526	840.83777	0	1012.36377	-1012.36377	0
100	111G	4385	1305.27	704.84	1674.45	720	3099.29	971	590.257	1488.76421	1012.475	3091.49621	1293.50379	0.295
101	105A	816	131.48	71	300.52	150	521.52	96	59.232	256.97525	100	416.20725	399.79275	0.4899
102	149S	469	71.65	38.7	156.54	190	385.24	118	72.806	151.18052	100	323.98652	145.01348	0.3092
103	16P	552	75.5	40.77	165.84	190	396.61	105	64.785	146.86475	100	311.64975	240.35025	0.4354
104	8H	2095	444.78	240.19	768.5	550	1558.69	371	228.907	657.20627	285.60917	1171.72244	923.27756	0.4407
105	20T	3137	1025.53	553.78	1375.12	150	2078.9	880	542.96	1271.55576	151.4563569	1965.972117	1171.027883	0.3733
106	79A	1003	140.44	75.84	209.59	360	645.43	116	71.572	184.46566	100	356.03766	646.96234	0.645
107	62J	277.02	0	0	0	0	0	0	0	201.65	100	301.65	-24.63	-0.0889
108	92N	1566	268.13	144.79	468.41	360	973.2	342	211.014	426.71287	283.255174	920.982044	645.017956	0.4119
109	30D	80	0	0	0	0	0	0	0	59.49	0	59.49	20.51	0.2564
110	45S	1026	171.43	92.57	376.08	550	1018.65	225	138.825	257.35114	139.8735124	536.0496524	489.9503476	0.4775
111	18R	1487.64	317.63	171.51	617.31	380	1168.82	193	119.081	538.46963	120.888639	778.439269	709.200731	0.4767
112	61I	1155.96	0	0	0	0	0	0	0	0	0	0	1155.96	1
113	37K	686	93.92	50.72	175.2	275	500.92	99	61.083	154.19811	100	315.28111	370.71889	0.5404
114	79A	498	71.66	38.69	127.48	150	316.17	97	59.849	118.14847	100	277.99747	220.00253	0.4418
115	17Q	0	0	0	0	0	0	0	0	0	0	0	0	0
116	30D	200.4	0	0	0	0	0	0	0	149.37	0	149.37	51.03	0.2546
117	92N	920	141.99	76.68	244.87	360	681.55	123	75.891	219.93086	166.4078928	462.2297528	457.7702472	0.4976
118	16P	4422	0	0	0	0	0	0	0	2823.48	274.1973946	3097.677395	1324.322605	0.2995
119	17Q	0	0	0	0	0	0	0	0	0	0	0	0	0
120	17Q	0	0	0	0	0	0	0	0	0	0	0	0	0
121	64L	1054	0	0	0	0	0	0	0	811.67	0	811.67	242.33	0.2299
122	45S	1146	193.4	104.44	395.76	275	775.2	232	143.144	347.14339	156.2329875	646.5203775	499.4796225	0.4358
123	109E	1703	381.02	205.75	767.55	380	1353.3	359	221.503	673.86197	105.3487954	1000.713765	702.2862346	0.4124
124	16P	642	104.91	56.65	208.24	190	454.89	119	73.423	186.42903	100	359.85203	282.14797	0.4395
125	31E	2376	586.5	316.73	969.26	285	1570.99	457	281.969	846.54147	149.318993	1277.829463	1098.170537	0.4622
126	50X	567.84	0	0	0	0	0	0	0	403.53	0	403.53	164.31	0.2894
127	70R	0	0	0	0	0	0	0	0	0	0	0	0	0
128	17Q	2306	736	397.43	878.56	380	1655.99	975	601.575	822.08956	187.3902298	1611.05479	694.9452102	0.3014
129	61I	1013.48	0	0	0	0	0	0	0	0	0	0	1013.48	1
130	2B	311	52.38	28.28	87.84	150	266.12	60	37.02	75.43994	100	212.45994	98.54006	0.3168
131	23W	198.5	0	0	0	0	0	0	0	134.22	100	234.22	-35.72	-0.1799
132	23W	470.4	0	0	0	0	0	0	0	318.83	100	418.83	51.57	0.1096
133	16P	614	90.86	49.07	195.36	190	434.43	97	59.849	172.11759	100	331.96659	282.03341	0.4593
134	18R	162	76.77	41.45	106.68	150	298.13	261	161.037	105.11089	0	266.14789	-104.14789	-0.6429
135	16P	176	0	0	0	0	0	0	0	130.88	0	130.88	45.12	0.2564
136	16P	220.44	0	0	0	0	0	0	0	164.31	0	164.31	56.13	0.2546
137	23W	68	24.72	13.34	19.56	150	182.9	42	25.914	24.88974	100	150.80374	-82.80374	-1.2177
138	23W	0	43.81	23.66	49.96	150	223.62	51	31.467	55.40523	100	186.87223	-186.87223	0
139	50X	1397.76	0	0	0	0	0	0	0	993.3	0	993.3	404.46	0.2894
140	23W	150	0	0	0	0	0	0	0	0	0	0	150	1
141	32F	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

 

[WilliamPHII]

That's only the first few rows, and the problem doesn't show up if you use only those...

Can I attach the PowerBI file somehow??

Thanks!

William

 

[WilliamPHII]

Well, I guess I solved it. I'm not sure which thing did the trick, but I removed some unnecessary columns, as well as rounded most of the columns to two decimal places.

Thanks for looking at it!

William

 

[JustynaMK]

Hi William,

Oh wow! What a turn of events while I was asleep
I found myself in a similar situation on a few occasions - once I started preparing the source file and try to explain how to recreate the issue, I realized that I cannot really recreate it, or I simply found the issue myself while reviewing it.

Anyway, I'm very glad you figured it out! Take care.