Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a requirement where there are 2 tables Oct_20 and May_22. The first table is Oct table and second is May table. My requirement is to get a new table with difference of these two tables. Like for 2012 0-3 = -3(Laptop), 26-67=-41(Workstation) for 2013 2-26=-24(laptop), 8-14=-6(Workstation) and so on. Please suggest on how to achieve this.
This is how the data looks like in each table as shown below. I need to find diff between count of asset type in 1st table and count of asset type in seconf table table.
Thanks in Advance,
Neelofar.
Solved! Go to Solution.
Hi @neelofarshama ,
Please create the table.
NewTable =
VAR tab =
DISTINCT (
UNION (
SUMMARIZE ( May_22, May_22[Model Year], May_22[Type] ),
SUMMARIZE ( Oct_20, Oct_20[Model Year], Oct_20[Asset Type] )
)
)
RETURN
ADDCOLUMNS (
tab,
"DiffValue",
CALCULATE (
SUM ( May_22[Count of Type] ),
FILTER (
May_22,
May_22[Type] = EARLIER ( [Type] )
&& May_22[Model Year] = EARLIER ( [Model Year] )
)
)
- CALCULATE (
SUM ( Oct_20[Count of Asset Type] ),
FILTER (
Oct_20,
Oct_20[Asset Type] = EARLIER ( [Type] )
&& Oct_20[Model Year] = EARLIER ( [Model Year] )
)
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @neelofarshama ,
Please create the table.
NewTable =
VAR tab =
DISTINCT (
UNION (
SUMMARIZE ( May_22, May_22[Model Year], May_22[Type] ),
SUMMARIZE ( Oct_20, Oct_20[Model Year], Oct_20[Asset Type] )
)
)
RETURN
ADDCOLUMNS (
tab,
"DiffValue",
CALCULATE (
SUM ( May_22[Count of Type] ),
FILTER (
May_22,
May_22[Type] = EARLIER ( [Type] )
&& May_22[Model Year] = EARLIER ( [Model Year] )
)
)
- CALCULATE (
SUM ( Oct_20[Count of Asset Type] ),
FILTER (
Oct_20,
Oct_20[Asset Type] = EARLIER ( [Type] )
&& Oct_20[Model Year] = EARLIER ( [Model Year] )
)
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@neelofarshama , Create common dimensions Type and model year, join with both tables and the use measure sum/ count of asset type from both tables in a visual
example
Type = distinct(union(distinct(Table1[Type]),distinct(Table2[Type])))
Use matrix visual, as per need
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |