topic Re: Calculate Direct& undirect Amount in DAX Commands and Tips

Calculate Direct& undirect Amount

Anonymous — Sun, 19 Jun 2022 10:02:41 GMT

Hello Expers,
I want a help for writing DAX code that caluculat Direct and Undirect Amount as Dynamic by choosing any country in country of Origin slicer.
How?
- Direct Amount is: If I chooses China as A country of Origin in slicer, It give amount when Counry of Origin and country of Imports are China.
- Undirect: give amount when Counry of Origin is China and country of Imports is NOT China.

I gave you an Example for China, But I want a Dynamic for all countries in country of Origin based what I will choose in Country of Origin Slicer.

Thanks in Advanced.

Country of Origin	Country of Import	Amount
China	China	34
China	UAE	3445
UAE	UAE	5676
China	China	6768
China	UAE	342
UAE	KSA	132
China	UAE	323
Turkey	UAE	234
China	Russia	4545
China	India	76
Turkey	Turkey	76765

Re: Calculate Direct& undirect Amount

AlB — Sun, 19 Jun 2022 10:54:04 GMT

Hi Anonymous

Create these two measures and place them in card visuals. See it all at work in the attached file.

DirectAmount = VAR origin_ = SELECTEDVALUE(Table1[Country of Origin]) RETURN CALCULATE(SUM(Table1[Amount]),Table1[Country of Import] = origin_)

IndirectAmount = VAR origin_ = SELECTEDVALUE(Table1[Country of Origin]) RETURN CALCULATE(SUM(Table1[Amount]),Table1[Country of Import] <> origin_)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Calculate Direct& undirect Amount

Anonymous — Sun, 19 Jun 2022 17:25:20 GMT

Thanks in Advanced.
It worked correctly,
but If I want to show the indirect countries in bar chart?
Also, I have commodities column, then also I want to show top 5 commodites for indirect value for indirect countries.

I hope you understand me😅

Re: Calculate Direct& undirect Amount

AlB — Sun, 19 Jun 2022 17:48:32 GMT

Anonymous wrote:

I hope you understand me😅

I don't. Please show a sample of the data with all the columns involed ( I don't see commodities above) and explain further what the chart would show

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Calculate Direct& undirect Amount

Anonymous — Sun, 19 Jun 2022 18:13:18 GMT

I want to show:
1- Card: direct amount

2- Card: Indirect Amount

3- Bar Chart: indirect countries with indirect value
4- Bar Chart: top commodities for indirect countries amount
5- Pie Chart: point of entry for for indirect countries amount

Country of Origin	Country of Import	Commodity	Point of Entry	Amount
China	China	Multiple Loudspeakers	Sarfayt Land Border	34
China	UAE	Natural Gas, Liquefied	SOHAR (SEA PORT)	3445
UAE	UAE	Ball Bearings	Al Wajajah Land Border	5676
China	China	Electric Doorbells	Customs HQ	6768
China	UAE	Tableware Or Kitchenware	HAFEET (BORDER POST)	342
UAE	KSA	Parts Of Gas Turbines, N.E.S.	Sarfayt Land Border	132
China	UAE	Multiple Loudspeakers	KHATMAT MILAHA (BORDER POST)	323
Turkey	UAE	Skulcaps (Taqias)	Al Darah	234
China	Russia	Audio-Frequency Electric Amplifiers	Al Wajajah Land Border	4545
China	India	Dress Patterns	SUWEIQ (SEA PORT)	76
Turkey	Turkey	Inductors, N.E.S	AL DUQM (SEA PORT)	545
KSA	Oman	Motors Of An Output <= 37,5 W	SARFAIT (BORDER POST)	435
KSA	KSA	Multiple Loudspeakers	CENTRAL POST OFFICE	150
KSA	Japan	Electric Doorbells	Salalah International Airport Passenger	401
KSA	KSA	Natural Gas, Liquefied	DIBA PORT (BORDER POST)	164
KSA	India	Fuel oils For ships	Al Darah	191
USA	USA	Frozen Cuttle fish and squid	DIRECTORATE GENERA	393
USA	Brazil	Dress Patterns	Sarfayt Land Border	167
USA	Canada	Audio-Frequency Electric Amplifiers	ARKAN SOHAR LOGISTICS LLC NORTH	307
USA	USA	Sweet Biscuits	HAFEET (BORDER POST)	100
USA	USA	Hair Lacquers	ARKAN SOHAR LOGISTICS LLC NORTH	209
USA	Egypt	Household Ware Of Zinc	DIBA PORT (BORDER POST)	226
USA	Iraq	Sweet Biscuits	Sarfayt Land Border	484

Re: Calculate Direct& undirect Amount

AlB — Sun, 19 Jun 2022 20:06:52 GMT

Anonymous

Not sure I've understood the requirements correctly but see the attached file for a possible solution. Note the [IndirectAmount] measure has changed slightly:

IndirectAmount = VAR origin_ = SELECTEDVALUE(Table1[Country of Origin]) RETURN CALCULATE(SUM(Table1[Amount]),KEEPFILTERS(Table1[Country of Import] <> origin_))

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Calculate Direct& undirect Amount

Anonymous — Thu, 23 Jun 2022 05:05:48 GMT

Hello ALB again,
in this photo, I used merged bar chart, and I want to put tooltip that show % of each market for China and non China>>
For Example, Mineral product market , it shows me % of China and % of Non - China by the total value of mineral product market