Count and Sum of Balanced Orders and Tasks

[shrock777]

Hello,

This might be very simple issue but it turns out complex for me.Below is the table. I am trying to calculate tasks and orders which are balanced(which means sum Order Count = 0). 

Count of distinct Orders = Calculate(distinctcount(order no))

Count of Orders in Balance =

Calculate(distinctcount(order no)),Filter(SUM(Order/Task Quantity = 0))

Count of Orders not in Balance =

Calculate(distinctcount(order no)),Filter(SUM(Order/Task Quantity <> 0))

Count of distinct Tasks =  Calculate(distinctcount(task order no))

Count of Tasks in Balance =

Calculate(distinctcount(task order no)),Filter(SUM(Order/Task Quantity = 0))

Count of Tasks not in Balance =

Calculate(distinctcount(task order no)),Filter(SUM(Order/Task Quantity <> 0))

Expected Result (KPI's)

• Count of distinct Orders = 1
• Count of Orders in Balance = 0
• Count of Orders not in Balance = 1 
• Count of distinct Tasks = 7
• Count of Tasks in Balance = 5
• Count of Tasks not in Balance = 2

I have Order No as slicer, For example if I select Order No 1350000-11 then it should display Count of Orders in Balance = 1 and Count of Orders not in Balance  = 0. Same applies for the date range, If I select 

Counts of Balanced Tasks is visualized in clustered bar chart and interactions should work as expected

@Greg_Deckler , @TomMartens 

Please help me with above issue. Thanks!! 

Order No	Task Order No	Task Completed Date	Order/Task Quantity
1350000		8/9/19	0
1350000	1350000-11	7/31/19	0
1350000	1350000-12	8/1/19	0
1350000	1350000-13	8/9/19	0
1350000	1350000-14	8/9/19	0
1350000	1350000-15	8/9/19	0
1350000	1350000-8	8/1/19	-370
1350000	1350000-8	8/1/19	-90
1350000	1350000-9	7/31/19	0
1350000	1350000-9	7/31/19	4
1350000	1350000-9	7/31/19	95

[TomMartens]

Hey @shrock777 ,

I think that you have to re-think your solution.

The column chart in your report is only using a measure, it's not possible to make a measure the "origin" of an interaction between visuals, there is just one exception for this, one can use a measure as a "starting point" for a drillthrough or using the DETAILSROW function in combination with SSAS Tabular or Azure Analysis Services.

One approach could be to create a column inside your table called "balanced / not balanced", then use this column as the axis. As the measure you are using just filters for one of the two columns values there is just one column. For this reason, you can use the column to filter all your other visuals.

Hopefully, this provides some new ideas to tackle your challenge.

Regards,
Tom

[Greg_Deckler]

Perhaps:

Count of Orders in Balance =
  VAR __Table =
    GROUPBY(
      'Table',
      [Order No],
      "Balance",SUMX(CURRENTGROUP(),[Order/Task Quantity])
    )
RETURN
  COUNTROWS(FILTER(__Table,[Balance]=0))

The rest will be similar.

[shrock777]

Hello Greg,

Thanks for the reply. Counts are working perfectly but I have issue with interaction. When I click on the clustered bar chart(TNB) the TB and Count of Task should be 2 but they are not interacting. Below is the screenshot.

Thanks

Thanks

[Greg_Deckler]

Hard to say, nothing in the formula as I wrote it is messing with context.

[shrock777]

Greg, I am suprised too, because all the fields are from same table.  

Any help is appreciated @TomMartens , @Zubair_Muhammad @jdbuchanan71 @Greg_Deckler @Sean @MarcelBeug 

Thanks

[TomMartens]

hey @shrock777 ,

please upload the sample pbix to onedrive or dropbox and share the link.

Regards,

Tom

[shrock777]

Hello @TomMartens ,

Thanks for the reply, below is the link for file.

https://filebin.net/7du6mez10dpfkdlw

Thanks