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 client A the matrix must be :

 

Please could you help me.

 

Thanks all.

 

 

  • 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


2 Replies

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    2 years ago
    en % Magasin vs Web = 
VAR v = CALCULATE(SUM(Table[#ventes]), ALL(Table[Type vente]))
RETURN IF(v, DIVIDE(SUM(Table[#ventes]),v))
  • 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