Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement which is as follows:
I have Warehouse, ID, Units
Where Warehouse comes from Table1 and ID and Units were from Table2.
When i select Warehouse from Table1, Count(Distinct(ID) from Table 2and Sum(Units) from Table2 should be displaye d in table.
=Pick(Match([W1], 'ABC'), Sum(TOTAL{<W2= {'ABC'}>} [Units]))
=Pick(Match([W1], 'ABC'), Count(Distinct{<W2= {'ABC'}>} ID))
The above coed is not giving me right answer...
Can anyone look into this.
please post sample app/data
thanks
regards
Marco
What do you mean with 'not giving right answer'?
If you don't tell us by, it's just guessing around.
And what is field W1, maybe the Warehouse field?
If you select more then a single W1 value or you don't have any selection on this field, Match([W1],'ABC') will obviously not work even when 'ABC' is part of the possible values.
It's most of the time easier to help you if you could post a small sample QVW, or a sample INLINE data set and your expected result.
Try the following expressions instead:
=Pick(Match([W1], 'ABC')+1, Null(),Sum(TOTAL{<W2= {'ABC'}>} [Units]))
=Pick(Match([W1], 'ABC')+1, Null(),Count(Distinct{<W2= {'ABC'}>} ID))
Hi,
Here is the sample data.
Hi Sir,
I tried it.
is =Pick(Match([W1], 'ABC')+1, Null(),Sum(TOTAL{<W2= {'ABC'}>} [Units]))
same as
=Pick(Match([W1], 'a'), Sum(TOTAL{<W2= {'a'}>} [units])) ???
Can u pls lemme knw about the logic??
Hi,
Instead of joining you can use Concatenate() like below
Table:
LOAD ID,
W1 AS Warehouse,
units,
'Table1' AS Source
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Concatenate(Table)
LOAD ID,
W2 AS Warehouse,
units,
'Table2' AS Source
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
Now W1 and W2 are in single field and values are summed up when selected in this field.
To get Count and Sum of values use below expressions
Count({<Source={'Table2'}>}Distinct ID)
Sum({<Source={'Table2'}>}Distinct Units)
Regards,
Jagan.
Hi Sir,
Here is the application...
Sum is working but not Count....
using the same pick and match functions, can i please know how can i diaply the values of LBCD.
=Pick(Match([W1], 'OB1')+1, Null(),Count(Distinct{<[W2]= {'OB1'}>} LBCD))