Totals In different Hierarch levels

[Anonymous]

Hi

Please see below image:

My dimension hierarch from biggest to smallest are group label, category label and generic label. The total on category level is correct but it is not correct for the group total. For the group total I would like the average of the totals for each category level but instead im getting the average of all the generic labels. How can I fix this so that power bi takes the average of the category label totals to do the group label total. I tried multiple measures and im getting to the same result, see different measures I used below:

testrating =

CALCULATE('Measure Table'[Actual]/'Measure Table'[Total score],GROUPBY(Visits,Visits[Generic Label],DSM_Store_Visits[Category Label],DSM_Store_Visits[Group Label],

"max rating",AVERAGEX(CURRENTGROUP(),Visits[Field Rating])))

test =

VAR gen = ISINSCOPE(Visits[Generic Label])

var cat = ISINSCOPE(Visits[Category Label])

var grp = ISINSCOPE(Visits[Group Label])

var tr = 'Measure Table'[Actual]/'Measure Table'[Total score]

return

switch(

true(),

gen,tr,

cat,tr,

grp,tr,

tr)

Base measures used in the final measures:

Actual:

CALCULATE(SUM('Visits'[Field Rating]),'Visits'[Field Rating]=1)

Total Score:

CALCULATE(COUNT('Visits'[Field Rating]),'Visits'[Field Rating]<>2 &&'Visits'[Field Rating]<>-1)

The answer I should get is (100+100+77,78)/3=92,59


Your help would really be appreciated.

[Anonymous]

at the moment the grand total is the average of all the generic label values which I dont want as it is the lowest level

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

You can try this code to do that.

Measure =
SWITCH (
    TRUE (),
    HASONEVALUE ( 'Table'[catagory] ), AVERAGE ( 'Table'[value] ),
    HASONEVALUE ( 'Table'[Group] ),
        AVERAGEX (
            SUMMARIZE (
                'Table',
                [catagory],
                "AVG", CALCULATE ( AVERAGE ( 'Table'[value] ) )
            ),
            [AVG]
        ),
    AVERAGEX (
        SUMMARIZE (
            'Table',
            [Group],
            "A",
                AVERAGEX (
                    SUMMARIZE ( 'Table', [catagory], "AVG", AVERAGE ( 'Table'[value] ) ),
                    [AVG]
                )
        ),
        [A]
    )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Group LabelCategory LabelGeneric LabelNew measure

Customer Experience	B.1	B.1.1	100.00%
Customer Experience	B.1	B.1.2	100.00%
Customer Experience	B.1	B.1.3	100.00%
Customer Experience	B.1	B.1.4	100.00%
Customer Experience	B.1	B.1.5	100.00%
Customer Experience	B.1	B.1.6	100.00%
Customer Experience	B.1	B.1.7	100.00%
Customer Experience	B.1	B.1.8	100.00%
Customer Experience	B.1	B.1.9	100.00%
Customer Experience	B.1	B.1.10	100.00%
Customer Experience	B.1	B.1.11	100.00%
Customer Experience	B.1	B.1.12	100.00%
Customer Experience	B.1	B.1.13	100.00%
	B.1 Total		100%
Customer Experience	B.1	B.1.14	100.00%
Customer Experience	B.2	B.2.1	100.00%
Customer Experience	B.2	B.2.2	100.00%
Customer Experience	B.2	B.2.3	100.00%
Customer Experience	B.2	B.2.4	100.00%
Customer Experience	B.2	B.2.5	100.00%
Customer Experience	B.2	B.2.6	100.00%
Customer Experience	B.2	B.2.7	100.00%
Customer Experience	B.2	B.2.8	100.00%
	B.2 Total		100%
Customer Experience	B.3	B.3.1	100.00%
Customer Experience	B.3	B.3.2	100.00%
Customer Experience	B.3	B.3.3	100.00%
	B.3 Total		100%
Customer Experience	B.4	B.4.1	100.00%
Customer Experience	B.4	B.4.2	100.00%
Customer Experience	B.4	B.4.3	100.00%
Customer Experience	B.4	B.4.4	100.00%
Customer Experience	B.4	B.4.5	100.00%
	B.4 Total		100%
Customer Experience	B.5.1	B.5.1.1	100.00%
Customer Experience	B.5.1	B.5.1.2	100.00%
Customer Experience	B.5.1	B.5.1.3	100.00%
Customer Experience	B.5.1	B.5.1.4	100.00%
Customer Experience	B.5.1	B.5.1.5	100.00%
Customer Experience	B.5.1	B.5.1.6	100.00%
Customer Experience	B.5.1	B.5.1.7	100.00%
Customer Experience	B.5.1	B.5.1.8	100.00%
Customer Experience	B.5.1	B.5.1.9	100.00%
	B5.1 Total		100%
Customer Experience	B.5.2	B.5.2.1	100.00%
Customer Experience	B.5.2	B.5.2.2	100.00%
	B5.2 Total		100%
Customer Experience	B.5.3	B.5.3.1	100.00%
Customer Experience	B.5.3	B.5.3.2	100.00%
	B5.3 Total		100%
Customer Experience	B.5.4	B.5.4.1	100.00%
Customer Experience	B.5.4	B.5.4.2	100.00%
Customer Experience	B.5.4	B.5.4.3	100.00%
Customer Experience	B.5.4	B.5.4.4	100.00%
Customer Experience	B.5.4	B.5.4.5	100.00%
Customer Experience	B.5.4	B.5.4.6	100.00%
	B5.4 Total		100%
Customer Experience	B.5.5	B.5.5.1	100.00%
Customer Experience	B.5.5	B.5.5.2	100.00%
	B5.5 Total		100%
Customer Experience	B.5.6	B.5.6.1	100.00%
Customer Experience	B.5.6	B.5.6.2	100.00%
Customer Experience	B.5.6	B.5.6.3	100.00%
Customer Experience	B.5.6	B.5.6.4	100.00%
	B5.6 Total		100%
Customer Experience	B.6	B.6.1	100.00%
Customer Experience	B.6	B.6.2	100.00%
Customer Experience	B.6	B.6.3	100.00%
Customer Experience	B.6	B.6.4	100.00%
Customer Experience	B.6	B.6.5	100.00%
Customer Experience	B.6	B.6.6	100.00%
Customer Experience	B.6	B.6.7	100.00%
	B6 Total		100%
Customer Experience	B.7	B.7.1	100.00%
Customer Experience	B.7	B.7.2	100.00%
Customer Experience	B.7	B.7.3 1	100.00%
	B7 Total		100%
Customer Experience Total			100%
Processes	C.1	C.1.1	100.00%
Processes	C.1	C.1.2	100.00%
Processes	C.1	C.1.3	100.00%
Processes	C.1	C.1.4	100.00%
Processes	C.1	C.1.5	100.00%
Processes	C.1	C.1.6	100.00%
Processes	C.1	C.1.7	100.00%
Processes	C.1	C.1.8	100.00%
Processes	C.1	C.1.9	100.00%
	C1 Total		100%
Processes	C.2	C.2.1	100.00%
Processes	C.2	C.2.2	100.00%
Processes	C.2	C.2.3	0
Processes	C.2	C.2.4	0
Processes	C.2	C.2.5	N/A
Processes	C.2	C.2.6	100.00%
Processes	C.2	C.2.7	100.00%
Processes	C.2	C.2.8	100.00%
Processes	C.2	C.2.9	100.00%
Processes	C.2	C.2.10	100.00%
	C2 Total		77,78
Processes	C.3	C.3.1	100.00%
Processes	C.3	C.3.2	100.00%
Processes	C.3	C.3.3	100.00%
	C3 Total		100%
Processes Total			92,59
Grand Total			96,29
Category Total	Based on the average on the generic label value
Group total	Based on the average of the Category Label vale
Grand total	Based on the average of the group label value

Hi please assist. Those are the totals i am expecting. I am achieving the correct category and group totals but my grand total is incorrect.

[Anonymous]

Hi

this is still summarising the totals on the generic labels instead of the next hierarchy. The DAX in my previous comment works but it is still summarising the grand total on the average of the generic labels and i want it to summarize it on the average of the group labels

[Anonymous]

Hi
I got the result using the below measure but it is 100% correct:

New measure2 =

VAR Category =

ADDCOLUMNS(Summarize(Visits, DSM_Store_Visits[Generic Label],Visits[Category Label],Visits[Group Label]), "Total Rating",

CALCULATE(sum(Visits[Actual])/sum(Visits[Total Score]) ) )

return

averagex(Summarize(Category, [Category Label],Visits[Group Label]),[Total Rating])

Using this the group label and category label total is correct  but the overall total is incorrect. The overall total must take the average of the the group label. One of the group labels is processes and the other which is not shown above is customers. At the moment the total is taking all the aveage of all the category level totals and I want it to be a level higher. How would i do this?

[amitchandak]

@Anonymous , Try like

if(isfiltered(Visits[Group Label]) || ISINSCOPE(Visits[Group Label]) || ISINSCOPE(Visits[Category Label]) ,
switch(
true(),
gen,tr,
cat,tr,
grp,tr,
tr)
, averageX(Values(Visits[Category Label]), tr))