Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@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. |
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. |
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😅
@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. |
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 |
@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. |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |