Forum Discussion

PetraM's avatar
PetraM
New Member
2 years ago
Solved

How to group measures into categories

Hi all,

I am working on creating a table in a specific format and what I've done so far is not quite working. Hoping you could point me in the right direction quicker than it would take me to figure it out. 

I have a number of measures (for simplicity I called them A-B-C) and for each I have a 'QTD Actual' value, a 'Target', and a '% vs Target' (QTD Actual/Target). So my table currently looks like this: 

I need to organize this so I have the 'categories' on one axis (Actuals/Target/% vs Target) and the Measure names (A/B/C) on the other axis like so: 

I also have a number of slicers to filter the data. 

I have created a table with calculated columns to force it into the right format which looks good, however the filters don't work on this table. 

How would you go about doing this?

Many thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, PetraM 

    According to your description, you can try the following DAX to solve the problem. Using a matrix may be more relevant to what you are trying to accomplish.


    DAX:

    Actual = 
VAR _filter = SELECTEDVALUE('Table'[Category])
VAR _A_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "A"))
VAR _B_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "B"))
VAR _C_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "C"))

RETURN
SWITCH(
    _filter,
    "A",_A_actual,
    "B",_B_actual,
    "C",_C_actual
)

target = 
VAR _filter = SELECTEDVALUE('Table'[Category])
VAR _A_Target = IF(SELECTEDVALUE('Table'[Category]) = "A",1000)
VAR _B_Target = IF(SELECTEDVALUE('Table'[Category]) = "B",1100)
VAR _C_Target = IF(SELECTEDVALUE('Table'[Category]) = "C",1200)
RETURN
SWITCH(
    _filter,
    "A",_A_Target,
    "B",_B_Target,
    "C",_C_Target
)


% VS Target = 

VAR _result = DIVIDE([Actual],[target])
RETURN
_result

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, PetraM 

    According to your description, you can try the following DAX to solve the problem. Using a matrix may be more relevant to what you are trying to accomplish.


    DAX:

    Actual = 
VAR _filter = SELECTEDVALUE('Table'[Category])
VAR _A_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "A"))
VAR _B_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "B"))
VAR _C_actual = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] = "C"))

RETURN
SWITCH(
    _filter,
    "A",_A_actual,
    "B",_B_actual,
    "C",_C_actual
)

target = 
VAR _filter = SELECTEDVALUE('Table'[Category])
VAR _A_Target = IF(SELECTEDVALUE('Table'[Category]) = "A",1000)
VAR _B_Target = IF(SELECTEDVALUE('Table'[Category]) = "B",1100)
VAR _C_Target = IF(SELECTEDVALUE('Table'[Category]) = "C",1200)
RETURN
SWITCH(
    _filter,
    "A",_A_Target,
    "B",_B_Target,
    "C",_C_Target
)


% VS Target = 

VAR _result = DIVIDE([Actual],[target])
RETURN
_result

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum