replication of elementary P function of Qlik in PowerBI

[SagarJagga]

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

[bhanu_gautam · Accepted solution]

@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

[SagarJagga]

Hi Gautam,

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

[bhanu_gautam]

@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]
)
}
)
)

[SagarJagga]

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

Much appreciated

[SagarJagga]

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"