Anonymous

Not applicable

4 years ago

Solved

Totals In different Hierarch levels

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.

Need Help

v-chenwuz-msft

4 years ago

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.

Totals In different Hierarch levels.pbix29 KB

6 Replies

amitchandak
Super User
4 years ago
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))
Anonymous
Not applicable
4 years ago
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?
Anonymous
Not applicable
4 years ago
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

Not applicable

4 years ago

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

Not applicable

4 years ago

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

Community Support

4 years ago

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.

Totals In different Hierarch levels.pbix29 KB

Forum Discussion

Totals In different Hierarch levels

6 Replies

Related content

Different total

Hierarchal Relationships

Hierarchical Groups Design - best practices

Allocating a cost total across different line items

Hierarchal sorting