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)
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
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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]

       

      )

      )