cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shungong Frequent Visitor
Frequent Visitor

Using Dynamic Measures to recalculate visualized data

Hi,

 

I struggle to create a visualization that updates an average value to weeks based on a Dynamic Measure.

 

I'm creating a dashboard to keep track of %usage of each person in a team. Each set of data (row) contains, [Hours], [Person], [EndOfWeek], [Week Number]. [Project], [ProjectType] that are in my table 'JobTime'

 

Doing this, I calculate total number of 'Hours' per Person, Per Week (37.5h).  Visualizing this, I want to choose week numbers, and get a percentage on %utilization of a person based on the week interval I have chosen. 

 

1. I created a Measure: [WeekIntervals] = Distinctcount(JobTime[Week Number]) to count no. of weeks.

2. I make a slicer to adjust the week interval and a Power BI Card that updates [WeekIntervals] based on what I slice.

3. I calculate no. of hours per person per week as a percentage by JobTime[Hours]/37.5/[WeekIntervals].

 

[WeekIntervals] is dynamic when looking at the Power BI Card, but not in data when I've put the Measure into the DAX Formula.

 

Is there any way to make a Dynamic Measure to work in a DAX formula when visualizing data? See figure below.

 

Thanks

 

 

Capture.PNG

5 REPLIES 5
shungong Frequent Visitor
Frequent Visitor

Dynamic Measure to be part of a DAX formula

Hi,

 

 

I'm creating a dashboard to keep track of %usage of each person in a team. Each set of data (row) contains, [Hours], [Person], [EndOfWeek], [Week Number]. [Project], [ProjectType] that are in my table 'JobTime'. 37.5 hours are the hours billed per person per week. I made a measure called [WeekInterval] that is the number of distinct weeks in [Week Number]. 

 

Bar plot below are hours per person, per week. The Values are calculated as [Hours]/37.5/[WeekInterval]. I want the bar plot values to update as I change the the slicer to account for different week intervals. Currently, this doesnt work, as it in the formula will just act as a static value even when I adjust my slicer. 

 

Is there any way a Measure can change Values dynamically as a variable? 

 Capture.PNG

 

Thanks,

Shun

v-yulgu-msft Super Contributor
Super Contributor

Re: Using Dynamic Measures to recalculate visualized data

Hi @shungong,

 

Please try this:

[WeekIntervals] =
CALCULATE ( DISTINCTCOUNT ( JobTime[Week Number] )ALLSELECTED ( JobTime ) )

 

For more advice, please share some dummy data so that I can reproduce your scenario on my side.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jessica_Seiya Established Member
Established Member

Re: Dynamic Measure to be part of a DAX formula

Did you try SELECTEDVALUE in your measure? This DAX can capture the value user selected in the report

shungong Frequent Visitor
Frequent Visitor

Re: Using Dynamic Measures to recalculate visualized data

Hi @v-yulgu-msft,

 

Thanks for your response. I tried using formulas below to generate the plot.

 

Week Interval = calculate(distinctcount(JobTime[Week Number]),ALLSELECTED(JobTime))

Values  = HoursPercentage = JobTime[Hours]/37.5/[Week Interval]

 

But it still doesnt update when I filter my list using Slicer. In the example below, I should get 84% on the person on top row, but I'm getting 18%. It's dividing by the total number of weeks in the list rather than the sliced number of weeks. Data and an example where slicer filters to 2 weeeks. It divides by [Week Interval]=9 instead of 2

 

 

Data.PNGCapture 2.PNGnumber of week numbers that are filtered.

 

 

shungong Frequent Visitor
Frequent Visitor

Re: Dynamic Measure to be part of a DAX formula

Hi Jessica,

 

Thanks for your response.

 

I tried to make a separate column that calculates a uniform value of distinctcount([Week Number]).  But this uniform value in the column doesnt change by filtering.

 

So if I use SELECTEDVALUE, it would be selevcting a constant value of 9, instead of a dynamic value. 

 

Was this what you meant?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 392 members 4,389 guests
Please welcome our newest community members: