topic Re: replication of elementary P function of Qlik in PowerBI in DAX Commands and Tips

replication of elementary P function of Qlik in PowerBI

SagarJagga — Mon, 24 Jun 2024 18:24:58 GMT

Hi Team,

I want your help in writing a dax query. My requirement is given below -

My data

segement	sales	manufacturer
midsize uv	100	A
midsize uv	200	B
premium full size	400	B


	expected sum 300 since ONLY midsize uv is the part of A

I want to find out the sum of sales for all those segments where manufacturer is equals to A e.g midsize UV is the part of A manufacturer so total sum of sales would be 100 +200 = 300. premium full size is not part of A so it is ignored

Re: replication of elementary P function of Qlik in PowerBI

bhanu_gautam — Mon, 24 Jun 2024 18:33:16 GMT

SagarJagga , You can create a measure for this

SumOfSalesForA =
CALCULATE (
SUM ( YourTable[sales] ),
FILTER (
YourTable,
YourTable[segment] IN {
SELECTCOLUMNS (
FILTER (
YourTable,
YourTable[manufacturer] = "A"
),
"segment", YourTable[segment]
)
}
)
)

Re: replication of elementary P function of Qlik in PowerBI

SagarJagga — Tue, 25 Jun 2024 08:09:18 GMT

Hi Gautam,

Getting an error "A table of multiple values was supplied where a single value was expected"

Re: replication of elementary P function of Qlik in PowerBI

SagarJagga — Tue, 25 Jun 2024 08:39:50 GMT

Hi Gautam,

Actually we have multiple segments which are coming under manufacturer A. so it is throwing an error "A table of multiple values was supplied where a single value was expected"

Re: replication of elementary P function of Qlik in PowerBI

bhanu_gautam — Tue, 25 Jun 2024 12:05:00 GMT

SagarJagga ,Please try this formula

DAXTotalSalesForSegmentsOfA =

VAR SegmentsOfA =

CALCULATETABLE(

VALUES('Table'[segement]),

'Table'[manufacturer] = "A"

)

RETURN

CALCULATE(

SUM('Table'[sales]),

'Table'[segement] IN SegmentsOfA

)

I have tried it and it is working attaching PBIX file

Re: replication of elementary P function of Qlik in PowerBI

SagarJagga — Tue, 25 Jun 2024 13:41:37 GMT

Thanks for your help, just wanted to know why selectColumn has not worked.

Much appreciated