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
Here, in date column it has generated on daily basis and the map date should generated for last third day. Finally I have to get the total sum for occupied and count and divide the total value (count/occupied) and create a new column as derived column.
Am facing issue to create a Derived column.
Regards,
Yuvaraj
Solved! Go to Solution.
Hello @Anonymous,
you can add it like this:
= IF( NOT( ISBLANK( Data[Mapdate] ) ), CALCULATE( DIVIDE( SUM( Data[occupied] ), SUM( Data[count] ) ), Data[date] >= EARLIER( Data[date] ) - 2, Data[date] <= EARLIER( Data[date] ), ALL( Data ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
If you table is like this, all columns already exsits like below in your dataset except "column"
then you can create a column using LivioLanzo's formula.
If you dataset isn't like this, please let me know.
Best Regards
Maggie
Hello @Anonymous,
you can add it like this:
= IF( NOT( ISBLANK( Data[Mapdate] ) ), CALCULATE( DIVIDE( SUM( Data[occupied] ), SUM( Data[count] ) ), Data[date] >= EARLIER( Data[date] ) - 2, Data[date] <= EARLIER( Data[date] ), ALL( Data ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi LivioLanzo,
Thanks for the reply.
Yes its working fine. I have another doubt similar to this, consider if i am having two tables(Table 1: Mapdate,Count ; Table 2: date,occupied) like below screens how we will join the Mapdate and date column and do the same calulation which we did earlier.
Regards,
Yuvaraj
Hi @Anonymous
What is the first table actually?
From your screenshot, it seems it have applied some options like "Merge cells".
Does your table look like this after "unmerged cells"?
Mapdate | Count |
12/4/2018 | 8 |
12/4/2018 | 6 |
12/4/2018 | 2 |
12/7/2018 | 10 |
12/7/2018 | 10 |
12/7/2018 | 8 |
12/10/2018 | 15 |
12/10/2018 | 15 |
12/10/2018 | 15 |
Best Regards
Maggie
Hi
Yes I have appiled merge center but it wont repeat the values to clear on this i have make it as zero. Please refer the below screenshot and let me know if anything required.
Regards,
Yuvaraj
Hi @Anonymous
In Queries Editor,
Copy table1, in Table1_copy, “Group by”,
in Table2, merge queries, then expand all
In Table2, click on the “down-arrow” of the “date” field name, sort ascending.
Then click on “Table 1 _copy.Map date”, select Fill->Up,
Also fill up for the column “Table 1 _copy.count”
Close&&Apply
Create columns
Column = CALCULATE(SUM('Table 2'[occupied]),ALLEXCEPT('Table 2','Table 2'[Table 1 _copy.Map date])) Column 2 = [Column]/[Table 1 _copy.count]
Best Regards
Maggie
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |