Forum Discussion
calculate difference between two separate filtered measure
Hi Georgetimes ,
You are trying to filter to see the difference between the different customer regions in table 1 and table 2, right, and since I don't understand the structure of your two tables, I'll try to use your example data to try to recreate your problem as best as I can.
BookingsTable1 =
CALCULATE(
COUNTROWS('Table'),
FILTER('Table','Table'[Customer] IN {"A", "B"})
)
BookingsTable2 =
CALCULATE(
COUNTROWS('Table'),
FILTER('Table','Table'[Customer]IN{"C","D"})
)
Difference =
VAR Table1Bookings = [BookingsTable1]
VAR Table2Bookings = [BookingsTable2]
RETURN
IF(
ISBLANK(Table1Bookings) && NOT(ISBLANK(Table2Bookings)),
-100,
IF(
NOT(ISBLANK(Table1Bookings)) && ISBLANK(Table2Bookings),
100,
Table1Bookings - Table2Bookings
)
)
You can see that I set up two MEASURES to make a judgment, I chose two different customers to compare and also I tried to divide the customers of AB and CD into two tables, but after dividing them into two tables, I could not get the correct country field in the matrix, so I tried to write all the COUNTRY fields in one table and then pass a filter to make a judgment. It can fulfill your requirement.
I hope my thoughts have been helpful, and if you have further questions, feel free to contact me and I'll get back to you the first time I hear from you!
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you very much for taking your time to help me with this, I really appreciate it.
This helps me with a part of my issue, however I'm afraid it's not exactly what I was looking for.
- the first two tables should show the count of bookingId. This will be an easy fix, however wanted to mention it in case it will affect the next steps :
- in table1 Belgium should show 2, UK 2, France 1 and Italy 1
- in table 2 Italy 1, Belgium 1
- I'm not sure if this is correct, but BookingsTable1 and BookingsTable2, both measures might need a different approach as I'm trying to have this fully dynamic. I'm not sure if this will work if I'm selecting for Table1, only customerB, or customer C and D. The "user" will select what Customer he wants to see in table1 and table2 (therefore, the filters). I've tried to play with your PBI example and Table3(the difference table) didn't show the right countries.
I've selected only customer B (table1) and customer D (table2) and it was supposed to display Belgium, France, UK and Italy. Italy was not showing. ( it should show all countries, just once, no matter if it's in both tables or only in one). It looks likeSee screenshot below:
- this leads me to the next thing. The difference table. This should show the booking difference between table1 and table2. As per the above screenshot, it should show:
- Belgium 0 (table 1 has 1 booking for Belgium minus table2 which has 1 booking as well)
- France 100 (France is not in table2, therefore 100)
- UK 100 (Again, UK not in table2, therefore 100)
- Italy -100 (this should show as minus 100 as Italy is only in table2, so the calculation would be 0 minus 100)
Hope this makes more sense and thank you very much once again for your help
Anonymous - any idea about how can I do the above?
Any help is highly appreciated. Thank you!
let
// Function to check if a page has actual data (not just headers)
PageHasData = (PageNumber as number) as logical =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" & Number.ToText(PageNumber),
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback, {{"Check", "TABLE.full-width-table > * > TR > :nth-child(1)"}}, [RowSelector = "TABLE.full-width-table > * > TR"]),
ValidRowCount = if PageData = null then 0 else Table.RowCount(PageData)
in
ValidRowCount > 1, // More than just the header row// Function to find the last valid page dynamically
FindMaxPage = () =>
let
PageNumbers = List.Numbers(1, 20), // Check up to 20 pages
ValidPages = List.FirstN(PageNumbers, each PageHasData(_)) // Stop when a page is blank
in
if List.Count(ValidPages) = 0 then 1 else List.Last(ValidPages),MaxPages = FindMaxPage(),
// Function to extract data from a page
GetPage = (PageNumber as number) =>
let
TargetURL = "https://find-and-update.company-information.service.gov.uk/register-of-disqualifications/B?page=" & Number.ToText(PageNumber),
TryPage = try Web.BrowserContents(TargetURL),
Fallback = if TryPage[HasError] then null else TryPage[Value],
PageData = if Fallback = null then null else
Html.Table(Fallback,
{
{"Name", "TABLE.full-width-table > * > TR > :nth-child(1)"},
{"DOB", "TABLE.full-width-table > * > TR > :nth-child(2)"},
{"Town", "TABLE.full-width-table > * > TR > :nth-child(3)"}
},
[RowSelector = "TABLE.full-width-table > * > TR"]
)
in
PageData,// Generate only the valid pages dynamically
PageNumbers = List.Numbers(1, MaxPages),// Fetch and process data from valid pages
AllPagesData = List.Transform(PageNumbers, each GetPage(_)),
CombinedData = List.RemoveNulls(AllPagesData),
AppendData = Table.Combine(CombinedData),
// Clean and transform data
#"Removed Top Rows" = Table.Skip(AppendData, 1),
#"Filtered Rows" = Table.SelectRows(#"Removed Top Rows", each not Text.Contains([Name], "Name (of dis")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"DOB", type date}})
in
#"Changed Type"