Forum Discussion

paulfink's avatar
paulfink
Post Patron
5 years ago
Solved

Spread total over rows

Hi guys,    I need have a measure that takes the value depending on the progress of a task.   I need to take them totals and spread it across a project. Project Task Value DESIRED 1 A ...
  • v-alq-msft's avatar
    5 years ago

    Hi, paulfink 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Value(Measure):

    Value = 
    var _pro = SELECTEDVALUE('Table'[Project])
    var _task = SELECTEDVALUE('Table'[Task])
    return
    SWITCH(
        _pro,
        1,
        SWITCH(
            _task,
            "A",10,
            "B",15,
            "C",20
        ),
        2,
        SWITCH(
            _task,
            "A",5,
            "B",10,
            "C",15
        )
    )

     

    You may create a measure like below.

    Desired = 
    var tab = 
    SUMMARIZE(
        ALL('Table'),
        'Table'[Project],
        'Table'[Task],
        "Value",
        var _pro = 'Table'[Project]
        var _task = 'Table'[Task]
        return
        SWITCH(
            _pro,
            1,
            SWITCH(
                _task,
                "A",10,
                "B",15,
                "C",20
            ),
            2,
            SWITCH(
                _task,
                "A",5,
                "B",10,
                "C",15
            )
        )
    )
    return
    SUMX(
        FILTER(
            tab,
            [Project]=SELECTEDVALUE('Table'[Project])
        ),
        [Value]
    )

     

    Result:

     

    Best Regards

    Allan

     

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