I've started out using B.I recently and I'm having issues with a situation that happens often at my workplace.
I have a data set about constructions that often involves more than one city. As an example with random data:
| Road | City | Length (miles) | Type | Category | Value ($) |
| John St. | A | 10 | Road | Wide | 1000 |
| Mary St. | B | 20 | Interstate | Wide | 2000 |
| Tucson St. | C/D | 40 | Road | Narrow | 4000 |
I have 3 roads I'm working on 4 different cities, since Tucson St. runs across two cities, so I've tried separating the data into:
| Road | City | Length (miles) | Type | Category | Value ($) |
John St. | A | 10 | Road | Narrow | 1000 |
| Mary St. | B | 20 | Interstate | Wide | 2000 |
| Tucson St. | C | 10 | Road | Narrow | 1000 |
| Tucson St. | D | 30 | Road | Narrow | 3000 |
I can do DISTINCTCOUNT in order to get the amount of Roads or Cities I'm working with to present my data and I can divide both my Length and Value between the cities, but I have a problem with other colums such as "Type" and "Category", of which I have several with lots of details. If I have any sort of visual that has those options, the total amount is higher than what it actually is. For this example, it'd say I have 3 Roads and 1 Interstate, when I'm only working with 2 Roads and 1 Interstate (same with 3 narrows vs. 2 narrows). How can I divide my data or maybe work on it in order not to count those again?
