Forum Discussion

pattajoh's avatar
pattajoh
Regular Visitor
6 years ago
Solved

Need urgent help

Hello,   Good day to everyone out there. I need a help from you.   Below shown is a table where I need to have a column ("Tax impacted Direct") after "% Direct" showing a value (% Direct * Total ...
Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    pattajoh 

    Try this modified measure please:

    Tax impacted Direct = 
VAR _INDIRECT = 
CALCULATE(
    SUM(Report[Indirect]),
    ALLEXCEPT(Report,'Code List'[Code]))

VAR _DIRECT = 
CALCULATE(
    SUM(Report[Direct]),
    ALLEXCEPT(Report,'Code List'[Code]))    
RETURN

IF( NOT(ISFILTERED('Sup List'[Sup])) && ISBLANK(_DIRECT),
    _INDIRECT,
 COALESCE(   

 _INDIRECT * 'Report'[% Direct], BLANK()))

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    pattajoh 

    plesae try

    Measure 2 = 
VAR _SUM=CALCULATE(sum(Report[Indirect]),ALLEXCEPT(Report,'Code List'[Code]))
RETURN IF([% Direct]=0 && NOT ISFILTERED('Sup List'[Sup]),_SUM,_SUM * 'Report'[% Direct]+0)
Fowmy's avatar
Fowmy
Super User
6 years ago

pattajoh 

Can you try this measure?

Tax impacted Direct = 
VAR _INDIRECT = 
CALCULATE(
    SUM(Report[Indirect]),
    ALLEXCEPT(Report,'Code List'[Code])
)
RETURN
   COALESCE(_INDIRECT * 'Report'[% Direct],0)



________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

pattajoh's avatar
pattajoh
Regular Visitor
6 years ago

Thank you so much Fowmy & Ryan for your help,

 

It worked, however, I have another condition. Where there is no "Direct" value  but with an "Indirect" provided, I would still like to 

see the "total" summed up for that "Code".

 

In this case, "Code 30" is what I am referring to.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards,

 

JP

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    pattajoh 

    Try this modified measure please:

    Tax impacted Direct = 
VAR _INDIRECT = 
CALCULATE(
    SUM(Report[Indirect]),
    ALLEXCEPT(Report,'Code List'[Code]))

VAR _DIRECT = 
CALCULATE(
    SUM(Report[Direct]),
    ALLEXCEPT(Report,'Code List'[Code]))    
RETURN

IF( NOT(ISFILTERED('Sup List'[Sup])) && ISBLANK(_DIRECT),
    _INDIRECT,
 COALESCE(   

 _INDIRECT * 'Report'[% Direct], BLANK()))

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon on the right if you like this reply 🙂

    YouTube, LinkedIn

    • jpattamthanam's avatar
      jpattamthanam
      Frequent Visitor
      6 years ago

      Hi,

       

      I have an older version of Bi installed on my machine...

       

      what would be an alternative option for COALESCE 

       

      Could you share an edit to the measure you helped me with.

       

      Regards,

       

      JP

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        jpattamthanam 

         

        After RETURN

         

        replace the following 

         

        IF( NOT(ISFILTERED('Sup List'[Sup])) && ISBLANK(_DIRECT),

            _INDIRECT,

        IF(   

         

        _INDIRECT * 'Report'[% Direct]=BLANK(),

         

        _INDIRECT * 'Report'[% Direct]

         

        )

        )

         

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    pattajoh 

    plesae try

    Measure 2 = 
VAR _SUM=CALCULATE(sum(Report[Indirect]),ALLEXCEPT(Report,'Code List'[Code]))
RETURN IF([% Direct]=0 && NOT ISFILTERED('Sup List'[Sup]),_SUM,_SUM * 'Report'[% Direct]+0)
    • jpattamthanam's avatar
      jpattamthanam
      Frequent Visitor
      5 years ago

      Hello Ryan,

       

      I have minor hickup in using the DAX you helped me with when changing the structure of matrix by 'Sup List'[Sup] and 'Code List'[Code]. The resultant  RETURN statement is giving zeros, however the total towards the bottom gives the Report[Indirect].

       

      Can you help me with this.

       

      Thank you,

       

      John

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        jpattamthanam 

        Do you want to change the sup and code places like below? Then you either have the direct value or indirect value. How to use the previous logic to calculate?