Total Value Missing in Pivot Table

[jmah17]

Hi, I am very new to Qlik Sense, I am having a pivot table as follows:

Dimension	Measure 1	Sum(Measure 2 )
Totals	-	25.00
A	10.00	2.00
B	2.50	5.00
C	3.50	15.00
D	4.00	3.00

I have no idea why the total value for Measure 1 is missing,  when I do Sum(Measure 1), the total value will appear but this is not I want to show, I just want to show exact value for Measure 1 from the data source and show the total value on top of it. 

Much appreciated if anyone could help. 

[rubenmarin]

Hi, when there is more than one value to be show Qlik shows null(), you need to apply some aggregation expression to return only one value to each cell.

It this case total row has 4 diffrent values, wich one do you want to show? You can use Max(), FirstValue(), Avg(), Concat()... there are a lot of funtions to convert many values in the single value you want to show in a cell.

You can use Dimensionaity() to detect the total row and apply the aggregation expression just in that row, ie:

If(Dimensionality=0, Sum([Measure 1]), [Measure 1])

[jmah17]

Hi @rubenmarin , thanks for your reply. I just want the column to show the exact data from the source without adding any expression, plus showing the total value of the column. (The total value of Measure 1)

I tried this, If(Dimensionality=0, Sum([Measure 1]), [Measure 1]) but it is not working. 

Really appreciate it if anyone could help. 

[rubenmarin]

Hi, sorry, Dimensionality needs () so: If(Dimensionality()=0, Sum([Measure 1]), [Measure 1]).

Also, [Measure 1] should be a field that really exists, if it's not a field set the expression used for Measure 1.