Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate Direct& undirect Amount

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 
ChinaChina34
ChinaUAE3445
UAEUAE5676
ChinaChina6768
ChinaUAE342
UAEKSA132
ChinaUAE323
TurkeyUAE234
ChinaRussia4545
ChinaIndia76
TurkeyTurkey76765
Help Request
  • AlB's avatar
    AlB
    4 years ago

    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.

     

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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😅

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        4 years ago
        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.