Forum Discussion

HenKau's avatar
HenKau
New Member
2 years ago
Solved

Combine dates as column headers with other column headers containing numbers

Hello together,

I am stuck on a matrix visualization.
To my preferred visualization:
I have names staggered by position as rows.
As columns I would like to show:

  1. the hours worked per week (that works).
  2. I would like to display the sum of the hours (works as row totals) and
  3. the value of the hours in a currency (I'm stuck here). Example for this would be: hours * cost per hour.

It should look like this:

Position26.01.202402.02.202409.02.2024Total HoursTotal Cost ex. VATTotal cost incl. VAT
Supervisor14388h*3€=24€24*1.19 = 28.56€
Worker5551530€35.70 €

 

Do you have a hint, how I can manage to show my table like this. Or if you need more information, please feel free to ask it.

 

Thank you very much in advance.

 

Hendrik

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi HenKau 

     

    Here you can try this:

    First of all, Create a new calculated table:

     

    Table 2 = UNION(VALUES('Table'[Date]),{"Total Hours","Total Cost ex. VAT","Total cost incl. VAT"})

     

    Then add a measure:

     

    MEASURE =
VAR _selectedvalue =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR _currentPosition =
    SELECTEDVALUE ( 'Table'[Position] )
VAR _hours =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            FORMAT ( 'Table'[Date], "m/d/yyyy" ) = _selectedvalue
                && 'Table'[Position] = _currentPosition
        )
    )
VAR _totalhours =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Position] = _currentPosition )
    )
RETURN
    SWITCH (
        _selectedvalue,
        "1/26/2024", _hours,
        "2/2/2024", _hours,
        "2/9/2024", _hours,
        "Total Hours", _totalhours,
        "Total Cost ex. VAT", _totalhours * 3,
        "Total cost incl. VAT",
            _totalhours * 3 * 1.19
    )

     

     Finally, add a matrix in the report view with these fields:

     

    Or you can unpivot the table in the power query:

    Select the Date column and click the Unpivot Columns in the Transform pane:

    Then add custom columns:

    The result is as follow:

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi HenKau - create below measure with sumx  to calculates the total cost by multiplying the Hours Worked by the Cost per Hour for each row.

    Total Cost = SUMX(
    'Table',
    'Table'[Hours Worked] * 'Table'[Cost per Hour]
    )

     

    if you need to include vat, then reuse the total cost and multiple with 1.19

    Total Cost Incl VAT = [Total Cost] * 1.19

     

    Hope it works.

    • HenKau's avatar
      HenKau
      New Member
      2 years ago

      Hello rajendraongole1,

      Thank you very much for the calculation method. My problem is, that I am not able to add this calculation  to my Matrix table.
      Please find below a screenshot of my Visual:

       

      Unfortunately, I cannot add the new Measure under Columns.

       

      What I try is kind of calculate the Total Cost for each Weekending column, but only show the Column Subtotal and not the cost calue it self in the table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi HenKau 

         

        Here you can try this:

        First of all, Create a new calculated table:

         

        Table 2 = UNION(VALUES('Table'[Date]),{"Total Hours","Total Cost ex. VAT","Total cost incl. VAT"})

         

        Then add a measure:

         

        MEASURE =
VAR _selectedvalue =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR _currentPosition =
    SELECTEDVALUE ( 'Table'[Position] )
VAR _hours =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            FORMAT ( 'Table'[Date], "m/d/yyyy" ) = _selectedvalue
                && 'Table'[Position] = _currentPosition
        )
    )
VAR _totalhours =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Position] = _currentPosition )
    )
RETURN
    SWITCH (
        _selectedvalue,
        "1/26/2024", _hours,
        "2/2/2024", _hours,
        "2/9/2024", _hours,
        "Total Hours", _totalhours,
        "Total Cost ex. VAT", _totalhours * 3,
        "Total cost incl. VAT",
            _totalhours * 3 * 1.19
    )

         

         Finally, add a matrix in the report view with these fields:

         

        Or you can unpivot the table in the power query:

        Select the Date column and click the Unpivot Columns in the Transform pane:

        Then add custom columns:

        The result is as follow:

         

         

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.