Group by another field not in the matrix

[Anonymous]

Hello Power BI Community

Please help with this development task.

I am trying to acheive the Total Out column by using the Hidden Field to group the numbers of the seperate results in the Detail coumn, that I am not wanting to include in the matrix in blue.

Once making the Total Out column, I can then made the graph on the right. The graph shows each Business Area but the figures from Total Out.

The issue is that I need to clever DAX to avoid having to include the Hidden Field in the marix that would also help me create the graph without using Hidden Field.

The data is held within the Excel file in the DataRequests tab and the Detail and Total Out is the sum of the No. Data Requests column therein.

Excel File

[v-chuncz-msft]

@Anonymous,

You may refer to the measure below.

Measure =
VAR g =
    SELECTEDVALUE ( Table1[Hidden Field] )
RETURN
    CALCULATE (
        SUM ( Table1[Detail] ),
        Table1[Hidden Field] = g,
        ALLSELECTED ( Table1[Busines Area] )
    )

[Anonymous]

Hi Sam
Thanks for the response, Unfortunately didn’t work. I did however manage to get it working using a Summarize table then joined the table to the main query to get the desired result.
Regards

[v-chuncz-msft]

@Anonymous,

It seems to work for me.

[Anonymous]

Hi Sam

I tried what you suggested again and I got the screenshot below and suffering from missing values.

capture

Have you got your pbix ?

Thanks

Jude

[v-chuncz-msft]

@Anonymous,

Yes, check if the values in [Hidden Field] match exactly.

[Anonymous]

Hi Sam

Did you attach something that I missed ?

You mentioned Yes but I didnt see anything attached.

Sorry if I missed it.

Jude