cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Calculating Percentage of Sales

Hi

 

I've been trying several times unsuccessfully and couldn't find similar post to resolve this issue. Appreciate any help you can do for anyone with similar problem.

 

Here are my sample data :

 

PERIODPNLVALUE
May-18RENT100
May-18UTILITIES101
May-18SUPPLIES80
May-18REVENUE400
Jun-18RENT101
Jun-18UTILITIES100
Jun-18SUPPLIES101
Jun-18REVENUE450

 

 

I want to create a table that can generate this info with DAX. I can do SUM/Calculate with column "TOTAL" but I can't do % of revenue successfully without creating individual measures (such as RENT as % of Sales = DIVIDE ( CALCULATE (SUM(VALUE), PNL="RENT", CALCULATE(SUM(VALUE), PNL="REVENUE"),0).

 

Since my actual fact table has over dozen metrics so it is not efficient creating one measure for each PNL metric. Is there any work around it?

 

 

PNLTOTAL % OF REVENUE
RENT20123.6%
UTILITIES20123.6%
SUPPLIES18121.3%
REVENUE850100.0%

 

Thank you in advance for your kind help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

@Anonymous

 

It looks like the denominator in your DIVIDE( ) is yielding a zero.

Be careful because the rows in your matrix visual are filtering the METRICS table and then you are filtering on top of that for "CONSOLIDATED NET SALES (NG)" in the denominator. That would be why you get the zeros everywhere except the 100% in the row for "CONSOLIDATED NET SALES (NG)".

Try this change in your measure:

 

PERCENTAGE OF SALES =
DIVIDE (
    [Actual PNL],
    CALCULATE (
        [Actual PNL],
        FILTER ( ALL ( METRICS[PNL] ), METRICS[PNL] = "CONSOLIDATED NET SALES (NG)" )
    ),
    0
)

 

and also make sure "CONSOLIDATED NET SALES (NG)" is spelled exactly as it is in your table

View solution in original post

12 REPLIES 12
Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

Hi @Anonymous

If I understand correctly what you need, you can do the following:

1. Set up a matrix visual in your report

2. Place [PNL] in rows of the matrix

3. Place this measure in values of the matrix for the TOTAL:

 

Total = SUM(Table1[VALUE])

4. Place this measure in values (slight variation of what you already had):

 

% of Revenue =
DIVIDE (
    SUM ( Table1[VALUE] ),
    CALCULATE ( SUM ( Table1[VALUE] ), Table1[PNL] = "REVENUE" )
)

  

Highlighted
Anonymous
Not applicable

Re: Calculating Percentage of Sales

@Thank you for answering, @AlB.

 

I did exactly the steps you indicated in my model but I couldn't get accurately the results (% of revenue). Though I can replicate the result by creating individual measures but it's not scalable.

Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

@Anonymous

 

What is the problem exactly in the result you get? 

Highlighted
Anonymous
Not applicable

Re: Calculating Percentage of Sales

 

@AlB

 

 

 2018-12-27_7-53-58.jpg

 

The result is 0%

 

Here's my DAX formula: 

 

PERCENTAGE OF SALES =
DIVIDE (
    [Actual PNL],
    CALCULATE (
        [Actual PNL],
        FILTER ( METRICS, METRICS[PNL] = "CONSOLIDATED NET SALES (NG)" )
    ),
    0
)

Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

@Anonymous

 

the code for your measure [Actual PNL] ??

Highlighted
Anonymous
Not applicable

Re: Calculating Percentage of Sales

@AlB 

yes

 

METRICS is a lookup table with unique PNL metrics (gross sales, returns, etc.)

Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

@Anonymous

I meant: can you show the code that you use for your measure [Actual PNL]?

 

Highlighted
Anonymous
Not applicable

Re: Calculating Percentage of Sales

@AlB

Sorry I misunderstood. 

 

Actual PNL = sum(EIW[Actual]) 

 

EIW is fact table with the sample layout : period, metric (PNL), value (actual)....

Highlighted
Super User III
Super User III

Re: Calculating Percentage of Sales

@Anonymous

 

It looks like the denominator in your DIVIDE( ) is yielding a zero.

Be careful because the rows in your matrix visual are filtering the METRICS table and then you are filtering on top of that for "CONSOLIDATED NET SALES (NG)" in the denominator. That would be why you get the zeros everywhere except the 100% in the row for "CONSOLIDATED NET SALES (NG)".

Try this change in your measure:

 

PERCENTAGE OF SALES =
DIVIDE (
    [Actual PNL],
    CALCULATE (
        [Actual PNL],
        FILTER ( ALL ( METRICS[PNL] ), METRICS[PNL] = "CONSOLIDATED NET SALES (NG)" )
    ),
    0
)

 

and also make sure "CONSOLIDATED NET SALES (NG)" is spelled exactly as it is in your table

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors