Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display ratio in row

Hi,

 

I want to display ratio in row. It likes the image following.

But I only realized ratio in column. I created a measure following to calaculte ratio.

Ratio % = DIVIDE(CALCULATE('Query1'[Actual N], FILTER('STAR_Agregated_group_PL','STAR_Agregated_group_PL'[ldesc2]="Net margin")),
CALCULATE('Query1'[Actual N],FILTER('STAR_Detailed_group_PL', 'STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")))

I don't understand why the sublevel of "NET ACTIVITY TURNOVER" calculate ratio too. 

 

Could you help me?

I am looking forward to hearing from you soon.

 

Best regards.

 

 

General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Ashish_Mathur 

     

    I realized my expected result following:

     

     

    Actual N (New) =
    IF(
        FIRSTNONBLANK('Table ldesc2'[ldesc2], 1) = "Ratio %" && NOT( HASONEFILTER(STAR_Detailed_group_PL[ldesc2]) ),
        FORMAT(
        CALCULATE(
            'Query1'[Actual N],
            FILTER(ALL('STAR_Agregated_group_PL'),'STAR_Agregated_group_PL'[ldesc2]="Net margin")
        )
        /
        CALCULATE(
            'Query1'[Actual N],
            FILTER(ALL('STAR_Detailed_group_PL'),'STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")
        ), "00%"),
        [Actual N]
    )

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Ashish_Mathur 

       

      I realized my expected result following:

       

       

      Actual N (New) =
      IF(
          FIRSTNONBLANK('Table ldesc2'[ldesc2], 1) = "Ratio %" && NOT( HASONEFILTER(STAR_Detailed_group_PL[ldesc2]) ),
          FORMAT(
          CALCULATE(
              'Query1'[Actual N],
              FILTER(ALL('STAR_Agregated_group_PL'),'STAR_Agregated_group_PL'[ldesc2]="Net margin")
          )
          /
          CALCULATE(
              'Query1'[Actual N],
              FILTER(ALL('STAR_Detailed_group_PL'),'STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")
          ), "00%"),
          [Actual N]
      )
  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Your issue is referred to the red part why it have data been calculated ? it should be blank as other sublevel rows, right?

     

    Best Regards,

    Amy

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, v-xicai and Ashish_Mathur 

       

      Thank you for your reply. Yes, it's one of my questions. Why were the sublevels of NET ACTIVITY TURNOVER calculated?  I created a formula following to calculate Net margin / NET ACTIVITY TURNOVER

      Ratio % = DIVIDE(CALCULATE('Query1'[Actual N], FILTER('STAR_Agregated_group_PL','STAR_Agregated_group_PL'[ldesc2]="Net margin")),
      CALCULATE('Query1'[Actual N],FILTER('STAR_Detailed_group_PL', 'STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")))
       
      I want to put ratio under the Net margin or Total, it likes this:
       

      Could you help me?

      I am looking forward to hearing from you soon.

       

      Besta regards./

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, v-xicai and Ashish_Mathur 

      I created a measure following to calculate Net margin/NET ACTIVITY TURNOVER.


      Measure 3 = IFERROR(IF(

                      ISFILTERED('STAR_Detailed_group_PL'[ldesc2]) && NOT(ISFILTERED ('STAR_Account'[ldesc2])),

                      'Query1'[Actual N]

                      /

                      CALCULATE(

                          'Query1'[Actual N],

                           FILTER('STAR_Detailed_group_PL','STAR_Detailed_group_PL'[ldesc2]="NET ACTIVITY TURNOVER")

                  )

      ), BLANK())


      But I got a ratio following:

      But I don't know why NET ACTIVITY TURNOVER is calculated 100%. How to hide this 100%?

      I am looking forward to hearing from you soon.

      Best regards.

       

      • v-xicai's avatar
        v-xicai
        Community Support
        7 years ago

        Hi Anonymous ,

         

        How about the DAX below?

         

        Measure1= IF(MAX('STAR_Detailed_group_PL'[ldesc2])="NET ACTIVITY TURNOVER"&&,[Measure 3]<>BLANK(),BLANK(),[Measure 3])

         

        Best Regards,

        Amy