Forum Discussion

MiMich's avatar
MiMich
New Member
2 years ago
Solved

DAX formula

Hello everybody,   Here is the situation : I've got a table like this : I'd like to build this matrix in Power BI: There is also a filter on the client.  So if I select for example ...
  • BeaBF's avatar
    BeaBF
    2 years ago

    MiMich Hi! Here the measure to achieve the result:

    EventPercentage =
    VAR SelectedYear = SELECTEDVALUE('Table'[Year])
    VAR SelectedCategory = SELECTEDVALUE('Table'[Category])
    VAR SelectedTypeEvent = SELECTEDVALUE('Table'[Type Event])
    VAR SelectedClient = SELECTEDVALUE('Table'[Client])
    VAR TotalEvents = CALCULATE(SUM('Table'[#Event]), 'Table'[Category] = SelectedCategory,  'Table'[Year] = SelectedYear, ALL('Table'[Type Event]))
    VAR TotalEventsByYearAndType = CALCULATE(SELECTEDVALUE('Table'[#Event]), 'Table'[Category] = SelectedCategory,  'Table'[Year] = SelectedYear, 'Table'[Type Event] = SelectedTypeEvent, 'Table'[Client] = SelectedClient)

    VAR CALC =
    IF(
        TotalEvents = 0,
        BLANK(),
        DIVIDE(TotalEventsByYearAndType * 100, TotalEvents * 100, 0)
    )

    RETURN CALC

     

    also interacts with client slicer:

     

    if it's oki, please accept my answer as solution.

     

    BBF


BeaBF's avatar
BeaBF
Icon for Super User rankSuper User
2 years ago

MiMich Hi! Here the measure to achieve the result:

EventPercentage =
VAR SelectedYear = SELECTEDVALUE('Table'[Year])
VAR SelectedCategory = SELECTEDVALUE('Table'[Category])
VAR SelectedTypeEvent = SELECTEDVALUE('Table'[Type Event])
VAR SelectedClient = SELECTEDVALUE('Table'[Client])
VAR TotalEvents = CALCULATE(SUM('Table'[#Event]), 'Table'[Category] = SelectedCategory,  'Table'[Year] = SelectedYear, ALL('Table'[Type Event]))
VAR TotalEventsByYearAndType = CALCULATE(SELECTEDVALUE('Table'[#Event]), 'Table'[Category] = SelectedCategory,  'Table'[Year] = SelectedYear, 'Table'[Type Event] = SelectedTypeEvent, 'Table'[Client] = SelectedClient)

VAR CALC =
IF(
    TotalEvents = 0,
    BLANK(),
    DIVIDE(TotalEventsByYearAndType * 100, TotalEvents * 100, 0)
)

RETURN CALC

 

also interacts with client slicer:

 

if it's oki, please accept my answer as solution.

 

BBF