cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ssicefox Frequent Visitor
Frequent Visitor

Display ratio in row

Hi,

 

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

Capture.JPG

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. 

 

Ratio.PNG

Could you help me?

I am looking forward to hearing from you soon.

 

Best regards.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

Hi @Ashish_Mathur 

 

I realized my expected result following:

 

Ratio.PNG

 

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]
)

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Display ratio in row

Hi,

I do not understand your requirement.  What is your expected result?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
Community Support Team

Re: Display ratio in row

Hi @ssicefox ,

 

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

 

1.png

Best Regards,

Amy

 

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

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:
 
Capture - Copy.JPG

Could you help me?

I am looking forward to hearing from you soon.

 

Besta regards./

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

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())

Capture.PNG
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.

 

Community Support Team
Community Support Team

Re: Display ratio in row

Hi @ssicefox ,

 

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

 

ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

Hi @v-xicai ,

Thank you  for your suggestion.

I created a new measure using another suggestion:

 

Ratio % = IFERROR(
                   IF(
                       NOT(HASONEVALUE(STAR_Detailed_group_PL[ldesc2])),
                       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")
                                               )
                        ),
                    BLANK()
                    )
I got the result following.
 
test.PNGtest.PNG
 
Best regards.
 
Wei
Highlighted
ssicefox Frequent Visitor
Frequent Visitor

Re: Display ratio in row

Hi @Ashish_Mathur 

 

I realized my expected result following:

 

Ratio.PNG

 

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]
)

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 317 members 2,335 guests
Please welcome our newest community members: