Use result of visualization in calculation of second visualization

[DaleH]

I'm creating a report over three tables (attendance, event, group).

There are 4 slicers - at least one field from each table used in a slicer.  So, there are selection fields from 3 tables.

The Weekly Attendance column is created by measure:

1 Weekly Attendance = COUNTROWS(attendance)

I need to create a solution which counts the events per person and the retrieve the MAX for the event (in this case 5). 

I have tried several methods (MAXX, DISTINCTCOUNT, COUNTROWS, etc) and all get the desired result if I make them a measure in a second visualization.  But, when I add the measure to the first visualization, the count reflects the count of the rows for that attendee_id (see screen shot).  "attendee_id" is not one of the slicers.

How do I get the max (5 in this case) so I can create a "% Attendance" calculation column?

Thx for your help

[DaleH · Accepted solution]

I was able to solve the issue by creating the [Count Group Dates] measure to specifically include all the key values.  Taking a distinct count of the [occurrence] (which is a date and is not unique) after removing the limitation of the [attendee_id], I was able to get an accurate, distinct count of all dates for the event.  This value was then used to calculate the attendance %.

@Greg_Deckler : thx for the suggestions, they were helpful.

Weekly Attendance =

// Count the number of records in attendance_profiles by attendee_id for a date range)

COUNTROWS(detailed_attendance_12month_view)

 

Count Group Dates =

CALCULATE(

COUNTROWS(

DISTINCT(detailed_attendance_12month_view[occurrence])),

ALLEXCEPT(detailed_attendance_12month_view,

detailed_attendance_12month_view[group_id],

detailed_attendance_12month_view[group_name],

detailed_attendance_12month_view[occurrence],

detailed_attendance_12month_view[group_campus_name]

)

)

Group Attendance % =

var WA = [Weekly Attendance]

var CG = [Count Group Dates]

RETURN

// [Weekly Attendance] / [Count Group Dates]

WA / CG

[v-shex-msft]

Hi @DaleH,

It sounds like you want to extract aggregate results based on a specific filter on other visuals that not apply the current visuals, right?
Currently, you can't get different filter results based on slicers who used the same source field. (for this scenario, it will only get the filter result that directly apply to the current visual that your measure formula host on)

If not, you can use Greg_Deckler write summarize variable to summary records based on current row content with 'allselected' function or ignore raw filter effects with 'all' function. 

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

For multiple aggregates that apply on measure formula, you need to nested formulas with different summarize functions and corresponding categories.

Regards,

Xiaoxin Sheng

[DaleH]

@Greg_Deckler Thx for the quick reply.

The solution works outside the primay visualization and yields "5".  However, when I add the measure to the primary visualization, it becomes subject to the selection INCLUDING attendee_id and yields the same answer as the Weekly Attendance measure.

OR

Total Weeks = DISTINCTCOUNT(R_E_ap[occurrence_date]}

max Total Weeks = MAXX(FILTER(VALUES(R_E_ap[event_id]),[Total Weeks]),[Total Weeks])

Table 2 = VAR temptable = SUMMARIZE (R_E_ap ,R_E_ap[occurrence_date] ,"Weeks" ,SUM(R_E_ap[head_count]) ) RETURN CALCULATE(COUNTROWS(temptable))

[Greg_Deckler]

@DaleH Still not clear on what you want. If you don't want it subjected to some context filters within a visual, use ALL or REMOVEFILTERS. Tough to be specific with the limited information provided and that nothing is text that I can copy and therefore mock-up. Probably want to use ALL if you are wanting a % calculation.

The other option is to add your measure a second time to the visual, click the drop down arrow in the Fields area for that measure and then choose Show value as and then Percent of grand total.

[DaleH]

https://1drv.ms/u/s!Agh0oJTA2ebQnqEBJ-ilIbYhdp_Rqg?e=E5SyW2

@Greg_Deckler Ok, I figured out how to share a PBIX (see the link above).  There are two reports in this PBIX, DEV and Desired.  I incorporated your suggestion on adding the measure a second time and reflecting it as a percentage.  That actually does provide an accurate percentage, but I am still unable to use it as a variable to compute "Weeks Absent".  (The users want an additional slicer over the "Pct Attendance" and it is not available to be added to a slicer.)

What I need is the ability to compute

* "Weekly Attendance" (done)

* "Total Weeks" (failed)

* "Weeks Absent" = "Total Weeks" - "Weekly Attendance" (failed)

The "Weekly Attendance" is subject to the values in the visualization (attendee_id, last_name, first_name) and then to 4 slicers.

The "Weeks Absent" should be calculated by subtracting "Weekly Attendance" from the "Total Weeks".

The "Total Weeks" should be a countrows of all [occurrence_date] values as controlled by the 4 slicers.  (a valid result for [group_name] = "Grace Group:Everett P" is 5)

[DaleH]

@Greg_Deckler 

Would be most grateful for any further advice.  Thx for your help.

[Greg_Deckler]

@DaleH This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

So basically you will use SUMMARIZE or GROUPBY to essentially recreate your table visualization but in memory, in your case you would SUMMARIZE by attendee_id for example.