Generic Column based on other columns in Powerbi

[shubh_kush]

Hi Everyone,

I have one query in my Powerbi report. Below are the three different benefits (Benefit 1, Benefit 2 & Benefit 3) with another column Deployment Model in a table visual. (Ref: Table visual 1)

Table Visual 1:

                                            

I want to add another table visual in the same report, which will show all the Benefits in a single column (Generic - Benefit 1 + 2+ 3) and counts with respect to each Deployment Model. (Ref: Table Visual 2). 

Table Visual 2:

                                         

I'm not able to create the Generic Benefit column to have all the Beneifts. I tried creating a separate table and joined them together but I can only use one Benefit column to establish the relationship.

Note: There are other benefits as well (like Digital readiness, etc) which will come later in the data but should in the Generic column in Table Visual 2.

Thanks in Advance

[Samarth_18 · Accepted solution]

Hi @shubh_kush ,

You can create a seperate table for unique benefits as below

Table 2 =
VAR distnct_benefit1 =
    DISTINCT ( Deployment[Benefit 1] )
VAR distnct_benefit2 =
    DISTINCT ( Deployment[Benefit 2] )
VAR distnct_benefit3 =
    DISTINCT ( Deployment[Benefit 3] )
RETURN
    DISTINCT ( UNION ( distnct_benefit1, distnct_benefit2, distnct_benefit3 ) )

Now create a two measure one for onprem and another for cloud with below code

Cloud =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "Cloud"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

On_Prem =
VAR result =
    CALCULATE (
        COUNT ( Deployment[Deployement] ),
        FILTER (
            Deployment,
            Deployment[Deployement] = "On Prem"
                && (
                    Deployment[Benefit 1] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 2] = MAX ( 'Table 2'[Benefits] )
                        || Deployment[Benefit 3] = MAX ( 'Table 2'[Benefits] )
                )
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

Now add benefits column from newly created table and add above measure you will see required output as below

Thanks,

Samarth

[shubh_kush]

Your solution works perfectly fine 🙂

Thanks a lot. But the total for the column is not correct. Instead of total, it is showing Maximum.

[Samarth_18]

For getting correct total you can create another two measure apart from above mentioned measures.

cloud_total =
SUMX (
    SUMMARIZE ( 'Table 2', 'Table 2'[Benefits], "_cloud_sum", [Cloud] ),
    [_cloud_sum]
)

onprem_total =
SUMX (
    SUMMARIZE ( 'Table 2', 'Table 2'[Benefits], "_onprem_sum", [On_Prem] ),
    [_onprem_sum]
)

Update count measure as below:-

Count = [cloud_total] + [onprem_total]

Use these measures in table visual:-

Note:- Dont delete previously provided measure since we have used those measure only to get correct total.

Thanks,

Samarth

[amitchandak]

@shubh_kush , have you tried unpivot if possible

https://radacad.com/pivot-and-unpivot-with-power-bi

[shubh_kush]

I tried Unpivoting it but it will change the structure of the table and data. I'm looking for a solution by which I can create one single column with all the Benefits value, so that I can show count based on each Deployment type. The issue I'm facing with the 3 different benefits columns as I cannot create 3 differnet relationship between two tables.