Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
4 views
in BI by (17.6k points)

Problem

I would like to create a multi-layer histogram that shows the distribution of var1 on the first level and var2 on the second level, with a legend by source, like this: 

The value should show the percentage w.r.t. the total of a source, with all the selections and slicers applied. The percentages shown in the histogram should always sum to 100% per source.

Example data

I have the following example data:

source  var1    var2    count

A       1       1       100

A       1       2       12

A       1       3       34

A       2       1       1612

A       2       2       23

A       2       3       43

B       1       1       200

B       1       2       320

B       1       3       12

B       2       1       1757

B       2       2       345

B       2       3       32

What have I tried

I can achieve a total per source with the following measure without the filtering part:

percPerSource = 

DIVIDE(

    SUM(input[count]); 

    CALCULATE(

        SUM(input[count]); 

        ALLEXCEPT(input;input[source])

        )

    )*100 

If I turn on Drill mode and click on the columns of var1 I get the following, an undesired result (the percentages do not sum to 100%):

 

Another attempt was using the ALLSELECTED function:

percSelected = 

    DIVIDE(

        SUM(input[count]); 

        CALCULATE(

            SUM(input[count]); 

            ALLSELECTED(input[var1])

            )

        )*100


This shows only 100% on the var2 level:

1 Answer

0 votes
by (47.2k points)

percPerSource = DIVIDE( SUM(input[count]); CALCULATE( SUM(input[count]); FILTER( ALLSELECTED(input); input[Source] IN VALUES(input[Source]) ) ) )*100

  • This takes all the selected values as the universe you are filtering on but only selects the rows that in your local filter context.

Drilldown

  • The FILTER function takes a table as its first argument and a condition as the second argument. It iterates through every row in the table passed into it and checks if the condition holds and returns a table with only the rows where the condition evaluates to True.
  • The VALUES function returns a list of distinct values of the column specified evaluated within the local filter context.

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...