Get sum ignoring the relationship in Power BI

[jesly_ajin]

Dears,

There is Many- Many Relation between the Hour & Quantity table.

There is project and date and resource type (labor or equipment) slicer which is getting filtered on the Power bi graph.

I need the quantity for a task to be calculated as below.

Kindly help.

Now since in the Hour table there is only 4 lines, the quantity in power bi is calculated only for 4 lines.

I need the quantity to be calculated for each task irrespective of lines in hour table but must get filtered for project, date and resource type.

[v-yuta-msft]

@jesly_ajin ,

Create measures like pattern below:

Total_Qty =
CALCULATE ( SUM ( Quantity[qty] ), ALL ( Quantity ) )

Total_Qty =
CALCULATE ( SUM ( Hour[hours] ), ALL ( Hour ) )

Community Support Team _ Jimmy Tao

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

[jesly_ajin]

Hi @v-yuta-msft ,

ALL is ignoring the slicer filters too, i want the value to be filtered with slicer filter.

Will crossfilter help me with this? I need examples or links to understand it better.

[v-yuta-msft · Accepted solution]

@jesly_ajin ,

You may try dax below if you have a slicer on [id] column:

Total_Qty =
CALCULATE (
    SUM ( Quantity[qty] ),
    FILTER ( ALL ( Quantity ), Quantity[id] IN VALUES ( Quantity[id] ) )
)

Community Support Team _ Jimmy Tao

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

[jesly_ajin]

Please help. I am really in need for solutions and suggestions. Appreaciate your response