Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jadegirlify
Helper I
Helper I

Calculated Column based on Totals from Values

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

Original Table

DepartmentWork Location
HROnsite
HROnsite
HROnsite
HROnsite
Claims Onsite
Claims Virtual
UnderwritingOnsite
UnderwritingOnsite
UnderwritingVirtual
UnderwritingVirtual
UnderwritingVirtual
OperationsVirtual
OperationsVirtual

 

 

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

DepartmentTotal DepartmentWork LocationOnsiteVirtualTotal
HR13Onsite404
HR13Onsite404
HR13Onsite404
HR13Onsite404
Claims 13Onsite102
Claims 13Virtual012
Underwriting13Onsite205
Underwriting13Onsite205
Underwriting13Virtual035
Underwriting13Virtual035
Underwriting13Virtual035
Operations13Virtual022
Operations13Virtual022

 

Any assistance will be greatly appreciated. 

6 REPLIES 6
v-yifanw-msft
Community Support
Community Support

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:

vyifanwmsft_0-1718588952569.png

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.

IMG_0805.jpeg

@v-yifanw-msft 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
Super User
Super User

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

djurecicK2
Super User
Super User

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.

@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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors