Problem with total row in matrix

Question

Hi all,&nbsp;&nbsp;I have a Data that looks similar to this:TypeAmountNumberAverage PriceA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75&nbsp;&nbsp;Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 48&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 125&nbsp;&nbsp;&nbsp;Amount is just a SUM.&nbsp;Average Price y just an Average.Number isDIVIDE(AMOUNT;AVERAGE PRICE;BLANK())Like I wanted the total row Number represent the sum of the rows A, B and C and not the Division of Amount and Average Price, I did this:&nbsp;Number = IF(
    HASONEFILTER([Type]) ; DIVIDE([Amount] ; [Average Price] ; BLANK()) ; 
    SUMX (
            SUMMARIZE (
                VALUES ( [Type] );
                [Type];
                "AB"; DIVIDE ( [Amount]; [AVGPrice]; BLANK () )
            );
            [AB]
        ) And it works for that table.&nbsp;&nbsp;But the problem is when I want to represent the same data and measures but with a different dimension.&nbsp;Imagin that now I want to see it instead of by type, by Country, like this:CountryZXYTotalThe measure won't work because just works in case that type is filtered ...Any ideas to solve the problem that will work with all the dimensions of my model?Thank you in advance!!

zubair_muhammad · Answer

luxpbi&nbsp;Give this a shot&nbsp;Number =
IF (
    HASONEFILTER ( [Type] ),
    DIVIDE ( [Amount], [Average Price], BLANK () ),
    SUMX (
        ALLSELECTED ( TABLENAME ),
        DIVIDE ( [Amount], [Average Price], BLANK () )
    )
)

luxpbi · Answer

Zubair_Muhammad&nbsp;thanks for your answer but it doesn't work even with the [Type] table visual.And I think&nbsp;that your formula would work only with a table visual with the dimension [Type] and not with others.&nbsp;

luxpbi · Answer

Anyone wit more ideas? It's really urgent for me.&nbsp;If I don't find a solution I'll have to do a lot of different meassures, one for each dimension. And it's not efficient at all...

anonymous · Answer

HI&nbsp;luxpbi,
&nbsp;
Maybe you can try to use row count as calculate condition&nbsp;:
&nbsp;
Number =
IF (
    COUNTROWS(Table)=1,
    DIVIDE ( [Amount], [Average Price], BLANK () ),
    SUMX (
        ALLSELECTED ( Table ),
        DIVIDE ( [Amount], [Average Price], BLANK () )
    )
)
&nbsp;
&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Problem with total row in matrix

4 Replies

Related content

It's a matrix totalling problem!

Totals in Matrix

Matrix Visual Hierarchy Level Problem

Yet Another Matrix Total Problem

Matrix Totals Incorrect

Type	Amount	Number	Average Price
A	1.000	10	100
B	2.000	10	200
C	3.000	40	75
Total	6.000	48	125