Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hyugo_v
Helper I
Helper I

evolution in column

Hello everybody,

 

i'm currently working with power bi on a mission currently but i'm facing a problem which i still can't resolve, i put in attachment the illustration on what i will talk now.

 

I got a data source which contains lots of parameters but i reduced it on the attachment in order to be more understandable.

 

For each line , i have a projet, a start and a end of that projet, i got an ETC which basically means workload (hrs) necessary in order to complete that project, finally i got "t' which is use for speading the workload for each week, as we can see we couldn't use all the workload in the first week that's why we have the "41.79" hrs remaining for the second week.

 

I think you get the idea, i want a graph who represent the workload remaining through the weeks

 

The problem is that the evolution is on column not on lines, it means i have to choose the date or the value of that date, how can i do to use all the data in order to have the vizualisation i'm looking for ?

 

Sorry for my english if it's not really understandable, i'll try to more clear if needed,

 

Here is the screenshot from an excel tab i create(the original datasource is excel too). 

Capture forum.PNG

1 ACCEPTED SOLUTION

Hi  @hyugo_v ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

Hi @hyugo_v ,

 

You can create a calculated table named DateKey .

 

DateKey = CALENDARAUTO()

 

Then, create measure like DAX below.

 

measure1= CALCULATE(SUM('Table'[Every day workload]),FILTER(ALLSELECTED('Table'),'Table'[NET START]<=MAX('DateKey'[Date])&&'Table'[FINISH DATE]>=MIN('DateKey'[Date])))

  

Finally, try to choose Clustered column chart visual to show the result, drag the DateKey[Date] on Axis and drag the measure1 on Value .

 

If I misunderstand it, could you please describe how to get the data of "t", you can also share your sample data or desired output screenshots for further analysis, or upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

3.png

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

First of all, thank you @v-xicai for responding to my message quickly , 

 

you explanation was really clear, i tried to do as you said  but when i want to select the table of "net start " and " finish date " it doesnt show up, do you have an idea ? 

 

I put the screenshot of the "t" value, for each week we multply hours per day * number of working days in a week, and we do the same operation for each week until we use all of the ETC.

I hope it's enough visible but the red line "Weekly workload" is the sum of all the ETC of each project for each week,

 

the result of that is shown in the graph under, 

 

i'll continue to look after it, if you need further information,

 

Best regards, 

 

Hugo

 

Here is the formulate of the value of "t"Here is the formulate of the value of "t"

weekly worload.PNG

Hi @hyugo_v ,

 

For the FILTER condition, you can create like DAX below, then you can select the table of "net start " and " finish date ".

 

FILTER(ALLSELECTED('Table_Wavegraph'),'Table_Wavegraph'[NET START]<=MAX('DateKey'[Date])&&'Table_Wavegraph'[FINISH DATE]>=MIN('DateKey'[Date]))

 

Could you please clarify or show your desired output, such as in which visual ,which field on X axis, which field on Y axis?

 

Best Regards,

Amy

Hi @v-xicai ,

 

the formula you said is right ! i didn't type it correctly, i have indeed a visualisation which is closer to what i want,

 

to answer the question you asked, i want in the X axis the evolution in time (1/Apr/19, 8/Apr/19 etc...) and in the Y axis the sum of ETC of the projects as we see just above the dates in yellow (3123, 2835 etc...) wabtec.PNG

 

Which normally leads to a visualisation like as follow, your formula looks really correct but i don't find the good value yet, however it's getting closer 🙂 sum of ETC (Y axis) through the time (X axis)sum of ETC (Y axis) through the time (X axis)

@v-xicai

i got a visualisation like this, i'll try to find why i got different values, until now thank you for your help 🙂 power bi.PNG

Hi  @hyugo_v ,

 

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.