Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulfink
Post Patron
Post Patron

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.

ProjectTaskValueDESIRED
1A1045
1B1545
1C2045
2A530
2B1030
2C1530

 

This is what i need based on my current table.

 

I have this measure which works:

 

 

Change Val Total = CALCULATE(SUM('Table1'[Value]), ALLEXCEPT('Table1', 'Table1'[Project], 'Table1'[Date]))

 

 

but the value needs to be a measure, as it is bringing back errors on other formulas.
 
I have my measure ready (Value1) i just dont know how to do the spread out formula.
 
How could i do this
 
 
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @paulfink 

 

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

Table:

a3.png

 

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:

a4.png

 

Best Regards

Allan

 

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

View solution in original post

8 REPLIES 8
v-alq-msft
Community Support
Community Support

Hi, @paulfink 

 

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

Table:

a3.png

 

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:

a4.png

 

Best Regards

Allan

 

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

amitchandak
Super User
Super User

@paulfink , where is date,

It should be like

 

Sub Total = CALCULATE(SUM('Table1'[Value]), ALLEXCEPT('Table1', 'Table1'[Project]))

@amitchandak I have already tried that and it does not work.

 

I need the value to be a measure so please reference it as Value1.

 

 

@paulfink ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandak there is already an example in my post

@paulfink , That is an image. Not the fastest way. I need to create data myself?

@amitchandak have you managed to find a solution?

updated

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.