Calculated Column based on Totals from Values

[Jadegirlify]

I need to create columns showing total count of work locations by department. I would like to have this output table below:

Original Table

Department	Work Location
HR	Onsite
HR	Onsite
HR	Onsite
HR	Onsite
Claims	Onsite
Claims	Virtual
Underwriting	Onsite
Underwriting	Onsite
Underwriting	Virtual
Underwriting	Virtual
Underwriting	Virtual
Operations	Virtual
Operations	Virtual

Result Table (Note: Total HR count is 4 and all 4 are onsite, that's why virtual is 0 etc)

Department	Total Department	Work Location	Onsite	Virtual	Total
HR	13	Onsite	4	0	4
HR	13	Onsite	4	0	4
HR	13	Onsite	4	0	4
HR	13	Onsite	4	0	4
Claims	13	Onsite	1	0	2
Claims	13	Virtual	0	1	2
Underwriting	13	Onsite	2	0	5
Underwriting	13	Onsite	2	0	5
Underwriting	13	Virtual	0	3	5
Underwriting	13	Virtual	0	3	5
Underwriting	13	Virtual	0	3	5
Operations	13	Virtual	0	2	2
Operations	13	Virtual	0	2	2

Any assistance will be greatly appreciated. 

[Anonymous]

Thanks @djurecicK2  for his prompt reply.
Hi @Jadegirlify ,
Based on the information you provided, you should use ALL to filter filters. You can modify your measures like below:

total all = 
CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )

LocationOnsite = 
VAR _department =
    SELECTEDVALUE ( 'Table'[Department] )
VAR _work_location =
    SELECTEDVALUE ( 'Table'[Work Location] )
VAR _count_onsite =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Department] = _department
                && 'Table'[Work Location] = _work_location
        )
    )
RETURN
    IF ( _work_location = "Onsite", _count_onsite, 0 )

Virtual = 
VAR _department =
    SELECTEDVALUE ( 'Table'[Department] )
VAR _work_location =
    SELECTEDVALUE ( 'Table'[Work Location] )
VAR _count_virtual =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Department] = _department
                && 'Table'[Work Location] = "Virtual"
        )
    )
RETURN
    IF ( _work_location = "Virtual", _count_virtual, 0 )

Total = 
VAR _department =
    SELECTEDVALUE ( 'Table'[Department] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Department] = _department )
    )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[Jadegirlify]

 

@Anonymous Thank you! I'm getting "Visual has exceeded available resources" error. Not sure if it's because the real data I'm working with has about 15k of rows and I'm supposed to be filtering only 49 rows from the data. I tried to create an index row as well, but the result table isn't showing an index row in chronological order-- like the one you created. But I understand t's because the original table you started with is 13 rows and the result table is 13 rows.

In a situation where the original table has thousands of row, how can be use an index column to show a result table of 13 with index table numbered from 1-13 instead of random numbers between 1 to 10s of thousands. Does that make any sense to you? See screenshot of my index. Could that be the reason why the measures you created in giving me an error?

[djurecicK2]

Ok. Can you post screenshots of what you did and the result?

[djurecicK2]

Hi @Jadegirlify ,

 Do you need this to be a calculated column? You could try using a Matrix visual and putting the DepartmentWorkLocation field in the columns field.

[Jadegirlify]

@djurecicK2 It didn't work with a Matrix, I was getting 1 in each row as on the Total Department column as the values for Onsite, Virtual and Total were also not accurate

[Jadegirlify]

Hi, it can be a matrix, i tried to do a matrix but the columns still weren't coming up right.