Combined total without un pivoting columns

[Ngars]

Hi, 

I am trying to create a total from multiple columns without having to up pivot said columns. 

Here is a sample of the data;

Employer	Industry Sector	Industry Sector 2	Industry Sector 3	Employer size category	Number of Cadets contracted	Contract execution date	Final monitoring report received	Financial year
Āhau NZ Limited	Information media and telecommunications				1	10/03/2020	5/10/2020	2019/20
All Area Scaffolding Limited	Construction				3	17/06/2019	29/11/2019	2018/19
Broadspectrum (New Zealand) Limited	Electricity, gas, water and waste services	Construction		50+	15		2/07/2020	2020/21
Alliance Services Limited	Construction				2	9/02/2020	20/08/2020	2019/20

Im trying to show totals for all three industry columns. An example would be the total for Contstructions would be 3.

I cant un pivot the columns because im calculating the "Number of Cadets Contracting" column to show totals for each employer, un pivoting would create duplicated rows and double the totals.

Any help would be much appriciated, open to other options that may achieve the same outcome.

Thanks.

[Ngars]

Hi @MFelix 

Ok awesome, thanks for that! With that slicer working it looks like the solutiion to my problem. 

[Ngars]

Ok, so creating a seperate table might not solve my issue and I also need the Idusries to be filtered by Financial Year.

[MFelix]

Hi @Ngars ,

The disconnected table is only forcing that the 3 columns are searched for the comoon field that is the industry, however if you look at the part of the formula where you have ALLSELECTED you may choose to add additional columns with other filters in this case add the FY:

Count of industry = 
VAR Industry_selection =
    VALUES ( Industry[Industry Sector] )
VAR Industry_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector], 'Table'[Employer] ,'Table'[Financial year]),
        'Table'[Industry Sector] IN Industry_selection
    )
VAR Industry_2_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector 2], 'Table'[Employer] , 'Table'[Financial year]),
        'Table'[Industry Sector 2] IN Industry_selection
    )
VAR Industry_3_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector 3 ], 'Table'[Employer] , 'Table'[Financial year]),
        'Table'[Industry Sector 3 ] IN Industry_selection
    )
RETURN
    COUNTROWS ( Industry_Count ) + COUNTROWS ( Industry_2_Count )
        + COUNTROWS ( Industry_3_Count )

Has you can see on the PBIX attach it also works on FY slicer.

This is a context problem and so to enforce the context you can add the columns you need to the values, I have made this approach to have the ALLSELECTED based on differents columns but you can also place the table name instead, however this can caus performance issues but result should be similar.

[Ngars]

I also have a slicer that filters FY, will creating a seperate Industry table affect this?

[MFelix]

The separate table should not affect any other values since it will only impact measures or visualizations where it's mentioned.

[MFelix · Accepted solution]

Hi @Ngars ,

One question regarding the calculation you need to achieve you are presenting on your example that the Total for Constructions would be 3 is this the count of rows that have the Construction on one of the Industry sector?

What is the result you are trying to achieve in the calculations, is it to be used in different types of calculations like SUM, AVERAGE, COUNT or to a specific one?

And how do you want to have the values filter is it by a single column or by all columns that you have?

I have made a simple exercise making a count of each sector.

• Created a disconnected table with industry I used the code below:

Industry =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( 'Table'[Industry Sector] );
            VALUES ( 'Table'[Industry Sector 2] );
            VALUES ( 'Table'[Industry Sector 3 ] )
        )
    );
    'Table'[Industry Sector] <> BLANK ()
)

Now I added the following measure:

Count of industry = 
VAR Industry_selection =
    VALUES ( Industry[Industry Sector] )
VAR Industry_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector]; 'Table'[Employer] );
        'Table'[Industry Sector] IN Industry_selection
    )
VAR Industry_2_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector 2]; 'Table'[Employer] );
        'Table'[Industry Sector 2] IN Industry_selection
    )
VAR Industry_3_Count =
    FILTER (
        ALLSELECTED ( 'Table'[Industry Sector 3 ]; 'Table'[Employer] );
        'Table'[Industry Sector 3 ] IN Industry_selection
    )
RETURN
    COUNTROWS ( Industry_Count ) + COUNTROWS ( Industry_2_Count )
        + COUNTROWS ( Industry_3_Count )

As you can see below I have the count for each industry correctly:

[Ngars]

Hi @MFelix,

The construction total is a count across all three industry columns. So twice in Industry Sector and once in Industry Sector 2.

The result im trying to achieve in a count of the Industry Values across the three industry columns and im also using a SUM to calculate the "Number of Cadets Contracted", hence why I couldnt use the up pivot method.

Your example looks like what I was trying to achieve. Ill have to run it in my main pbix file and see if it solves my issue.

Thanks