data in column formula

[Anonymous]

Hello,

I have recorded data time by event like showed below. I would like to get the % of RUN as RUN/(RUN+CHANGE). I do not manage to get it done using DAX

Event	Duration (h)
RUN	3
STOP	15
CHANGE	0
RUN	12
STOP	8
CHANGE	8
RUN	0
STOP	1
CHANGE	2
RUN	10
STOP	14
CHANGE	11

[MartynRamsden · Accepted solution]

Hi @Anonymous 

Try this:

% Run = 
VAR TotalRun =
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = "RUN"
)
VAR TotalChange = 
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = "CHANGE"
)
VAR Result = DIVIDE ( TotalRun, ( TotalRun + TotalChange ) )
RETURN Result

You'll need to replace the reference to Table1 with your actual table name.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution. 

[MartynRamsden · Accepted solution]

Hi @Anonymous 

Try this:

% Run = 
VAR TotalRun =
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = "RUN"
)
VAR TotalChange = 
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = "CHANGE"
)
VAR Result = DIVIDE ( TotalRun, ( TotalRun + TotalChange ) )
RETURN Result

You'll need to replace the reference to Table1 with your actual table name.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution. 

[Anonymous]

Thanks a lot!

Another question, as I have much more than 3 events, could it be possible to replace the manual typing in the formula by a link to dropdown list or slicer?

 Table1[Event] = "RUN" => replaces the "RUN" by a link to slicer?

[MartynRamsden · Accepted solution]

Hi @Anonymous 

If the denominator is always the sum of the selected event + the sum of the "CHANGE" event, then the expression below should work for you:

% Selected Event = 
VAR SelEvent = SELECTEDVALUE ( Table1[Event] )
VAR TotalSelEvent =
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = SelEvent
)
VAR TotalChange = 
CALCULATE (
    SUM ( Table1[Duration (h)] ),
    Table1[Event] = "CHANGE"
)
VAR Result = DIVIDE ( TotalSelEvent, ( TotalSelEvent + TotalChange ) )
RETURN Result

If you require something different, please provide more info and I'll be happy to help.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution. 

[Anonymous]

Hello,

When I create the measure :

VAR TotalSelEvent = CALCULATE(sum('Performance Event Listing'[Durée (h)]),'Performance Event Listing'[Arrêt reclassifié ]=[VAR SelEvent])

I have an error message: A function calculate has been used in a true/false expression that is used as a table filter expression. this is not allowed

[MartynRamsden]

Hi @Anonymous 

My previous answer should be added to your model as a single measure to give you the % of the selected event type vs,  the total.

If you want to add a new measure to calculate the total for the selected event type, use this:

Selected Event Total = 
VAR SelEvent = SELECTEDVALUE ( 'Performance Event Listing'[Arrêt reclassifié] ) 
VAR Result =
CALCULATE (
    SUM ( 'Performance Event Listing'[Durée (h)] ),
    'Performance Event Listing'[Arrêt reclassifié] = SelEvent
)
RETURN Result

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.