Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Guys
I'm studying powerbi but I couldn't come to a conclusion for the question below, I need some help to get started.
Any suggestions on how to do it, what word to look for or some tutorial.
The case, I create project overview, how much percent a task will be completed each month.
As pictured, I currently do it manually.
I create 4 pages (yellow) in powerpoint based on the table above. I copy the graphics for each page.
In PowerBI I would like to be able to click on the month (timeline?) and show me how many % would be in that month, maybe a dynamic column would be the case.
=IF(WORKDAY($E4;L$2)/$D4<0;0;IF(WORKDAY($E4;L$2)/$D4>1;1;WORKDAY($E4;L$2)/$D4))
I use for Percent in excel.
I powerpoin, manually.
Thanks
Solved! Go to Solution.
Hi @Peleias ,
According to your description, I download your sample, here's my solution.
1.Create a date table including all the date in the progress, don't make relationship between two tables.
2.Create a measure.
Progress =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 5, 22 ), MAX ( 'TP'[ May 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 6, 22 ), MAX ( 'TP'[ Jun 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 7, 22 ), MAX ( 'TP'[ Jul 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 8, 22 ), MAX ( 'TP'[ Aug 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 9, 22 ), MAX ( 'TP'[ Sep 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 10, 22 ), MAX ( 'TP'[ Oct 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 11, 22 ), MAX ( 'TP'[ Nov 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 12, 22 ), MAX ( 'TP'[ Dec 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2023, 1, 23 ), MAX ( 'TP'[ Jan 23] ),
0
)
3.Put the new date column in the timeline, the measure and project in the bar chart, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Peleias ,
According to your description, I download your sample, here's my solution.
1.Create a date table including all the date in the progress, don't make relationship between two tables.
2.Create a measure.
Progress =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 5, 22 ), MAX ( 'TP'[ May 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 6, 22 ), MAX ( 'TP'[ Jun 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 7, 22 ), MAX ( 'TP'[ Jul 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 8, 22 ), MAX ( 'TP'[ Aug 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 9, 22 ), MAX ( 'TP'[ Sep 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 10, 22 ), MAX ( 'TP'[ Oct 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 11, 22 ), MAX ( 'TP'[ Nov 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2022, 12, 22 ), MAX ( 'TP'[ Dec 22] ),
SELECTEDVALUE ( 'Date'[Date] ) = DATE ( 2023, 1, 23 ), MAX ( 'TP'[ Jan 23] ),
0
)
3.Put the new date column in the timeline, the measure and project in the bar chart, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Because of your measure I was able to make a lot of progress. Helped me a lot. And I'm trying to simplify in powerbi, making the excel formula inside powerbi, with ChicletSlider Is it possible? In the same style as your mensure;
I'm mentioning you here because I was only able to advance with your measurement.
=IF(WORKDAY($E4;L$2)/$D4<0;0;IF(WORKDAY($E4;L$2)/$D4>1;1;WORKDAY($E4;L$2)/$D4))
This formula uses workday in between Start Date and Date Selected (from ChicletSlicer in powerbi) and divides from Duration.
The IF of formula is for the result to be a minimum of 0 (0%) and a maximum of 1 (100%).
pbix https://drive.google.com/file/d/1A_l7kBNZ3uD8xXP5LYUbHzr1mPEz04Ac/view?usp=sharing
Expect result;
thank you, regards
(original post https://community.powerbi.com/t5/Desktop/Mensure-Porcentage-between-two-dates-with-ChicletSlicer/m-p...)
@v-yanjiang-msft thank you very much. I was trying with relationship but still no success. I was looking at a form with unpivot data, but your method is much better.
I used ChicletSlicer with your solution and it turned out great!
Maybe is that too much to ask, is it possible to merge your solution with @amitchandak ? The percentage formula inside powerbi?
I tried here for 2h hours, but the relationship between rows and columns is something that has blown my mind.
Hi @Peleias ,
If you want to get the correct Amik like below, you should add a new project column, PROJECT E1,PROJECT E2 and PROJECT E3 belong to one project, the same goes for R and T.
In Power Query it's easy to get it by below steps:
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Project", "Project - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Project - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Project - Copy.1", "Project - Copy.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Project - Copy.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Project - Copy.1", "ProjectGroup"}})
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
Thanks Amit, I already subscribed to your channel. But I don't think I got it with formula.
https://drive.google.com/file/d/1NRL8bp_rFSejJGA523nEGyfH8F_ZAXyJ/view?usp=sharing
@Peleias , Try a measure like
divide(Sum(Table[Duration]), calculate(Sum(Table[Duration]), filter(allselected(Table) , Table[Project] = max(Table[Project]))))
Percent of SubTotal or Total: https://www.youtube.com/watch?v=6jTildcV2ho&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=37
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
86 | |
75 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |