Count of calculated value in a hierarchy

[Anonymous]

Hi!

I have a table with some staff training information:

training_table

Staff Name	Technology	Sub-Technology	Training Level
Richard	Software	Software A	1
Richard	Software	Software B	3
Richard	Hardware	Hardware A	4
Richard	Hardware	Hardware B	4
Duilio	Software	Software A	2
Duilio	Software	Software B	5
Duilio	Hardware	Hardware A	1
Duilio	Hardware	Hardware B	2

now, I need to compute some measure that provides the count of staff members by training level.

To achieve this task I built a series of measures for each sub-level of technology, since it's fairly easy to count unfilteredrows:

Level 1 Sub = CALCULATE(COUNTROWS(training_table),'training_table'[Training Level] = 1)

...

Level 5 Sub = CALCULATE(COUNTROWS(training_table),'training_table'[Training Level] = 5)

but when it comes to the technology level, it gets rough: I tried to group and summarize the table, but it seems very difficult to get a count of filtered grouped table by the average value of a calculated column. Moreover, I don't actually know if is it possible to build a measure that can return the number of staff member per level that can work both for technology and sub-technolgy.

edit: the training level of technology level is the rounded up average of sub-technology levels.

thank you for the help!

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

I'm not sure whether it is what you need, but you can try these Measures.

Level Count by Sub-Tech =

CALCULATE (

    COUNT ( training_table[Training Level] ),

    FILTER (

        ALLEXCEPT ( training_table, training_table[Sub-Technology] ),

        training_table[Training Level] = MAX ( training_table[Training Level] )

    )

)

Avg Level Count by Tech =

VAR _value =

    SUMMARIZE (

        training_table,

        "avg",

            AVERAGEX (

                VALUES ( training_table[Sub-Technology] ),

                [Level Count by Sub-Tech]

            )

    )

RETURN

    ROUND ( _value, 0 )

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That was it!

Thank you!

[amitchandak]

@Anonymous , can you share the expected output?

[Anonymous]

Sure!

I need to produce two stacked graphs both with number of sfaff per training level. lets say that we have 9 people in the staff:

where level of technology level is the rounded up average of the sub-techonology values