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.
Hello All,
I have been trying to figure the following out for the last 2 days without any luck,
Basiclly what iam trying to achieve is
Client - has allocated 10 hours a month
4 hrs worth of work is generated
10-4hrs = 6 hrs remain
The following is what iam working with;
* Direct Query SQL
1. Create a dax formula to combine hours spent on a "client"
SLAHrsCalculatedPerClient = sla[MeasureTotalTimespentOrg]
2. Attempted to make another measure to minus the caculated time (but it doesnt show up)
SLAHrsMinusFromAllocated = sla[MeasureTotalTimespentOrg]- "SLA HOURS"
however if i manually type the following it minus's the correct amount
SLAHrsCalculatedPerClient = sla[MeasureTotalTimespentOrg]-5
Am i overlooking something with the above, or just not going about it the right way entirely?
i have tried looking at a few other ways of doing this without luck, I hope enough information is provided
So for presenting the data would you have customers on the rows then 3 measures in the values
[SLAHours], [TimeSpent], [TimeRemain]
Where
[SLAHours] = SUM(sla[**Name of Prepaid Hours Column**])
[TimeSpentBase] = Sum(**TableName**[**Name of Time Spent Column**])
[TimeSpent] = DIVIDE( [TimeSpentBase], [NumeratorSwitch])
[TimeRemain] = [SLAHours] - [TimeSpent]
**Indicates you need to chose the correct Table / Column name
Hello,
Thanks for working with me throguh this, but it doesnt look like i can do what i need it to do through graphs
https://community.powerbi.com/t5/Desktop/Make-a-column-not-sum-count/td-p/24672
this is the issue im faced with and from reading it does not seem like there is a fix or solution yet, i have tried to change to decimal number, text, whole number
im going to try and look for a custom graph and see if i can do it with that!
SLAHours = Sum(sla[SLA Hours])
- Problem is i cant add this up because the "SLA HOURS" is a set number which is set and differs (ie: some might be 10 hours, some 20)
ie: Client A has - 10 hrs
Client B has - 20 Hrs
When i sum it up- it just adds the "SLA Hours" up and gives me a number of 30
Hi @Wise1,
In addition, use the formula below to create a measure, and show it with the Client id on the report should also work.
SLAHrsCalculatedPerClient = SUMX ( sla, sla[MeasureTotalTimespentOrg] - sla[SLA Hours] )
Regards
Thanks for replying, it does not seem to work with the method above, but works when i do it with only lets say [SLA Hours]
Hi @Wise1,
You can use the SUM function in DQ mode if you go to Options -> DirectQuery and enable "Allow unrestricted measures in DirectQuery mode".
Regards
Just thought i would reply so people dont think i have forgotten about this thread
Still trying to pull it together, but getting closer! thanks everyone for their input so far!
But then when you put that in a table against client it should show you the total SLA hours by client
Hello,
Apologies for delay in reply - was driving back from interstate location
- The formula works (and minus's the hours) but it first Adds the specified hours allocated to the client
so ive setup 2 as a test
ClientA - 20 hrs
ClientB - 10 Hrs
When i setup the dax to minus the hours it works (as you can see in picture)
Is there a possibility for a "straight conversion" so to speak to pick up the value in the cell and just convert it into a calculated form? (vlookup doesnt seem to want to work)
That approach won't work as you need to apply the VALUE function as a calculated column not as a measure
Will see if I can come up with an alternative
I think if you type [SLAHrsCalculatedPerClient] you will get what you need
By typing sla to begin with you are referencing a table column rather than a measure
To get a measure start with a [
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |