Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

@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

13 REPLIES 13
Anonymous
Not applicable

the same data i need the following results in power pivot datamodal (DAX)

Row LabelsMayMay %JunJun %Grand Total
RENT10015%10113%201
REVENUE40059%45060%850
SUPPLIES8012%10113%181
UTILITIES10115%10013%201
Total Expeneses681100%752100%1433
AlB
Super User
Super User

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

  

Anonymous
Not applicable

@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.

@Anonymous

 

What is the problem exactly in the result you get? 

Anonymous
Not applicable

 

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

@Anonymous

 

the code for your measure [Actual PNL] ??

Anonymous
Not applicable

@AlB 

yes

 

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

@Anonymous

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

 

@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

Anonymous
Not applicable

@AlB

 

I also found a similar solution with this DAX, skipping METRIC lookup table entirely:

 

PERCENTAGE OF SALES = DIVIDE( [Actual PNL] , Calculate([Actual PNL], filter( ALLSELECTED(EIW), EIW[LPD Profit Loss Line]="CONSOLIDATED NET SALES (NG)")),0)

@Anonymous

 

Cool Smiley Happy

Bear in mind that the 

   PNL="REVENUE"

as filter argument for the CALCULATE that you had in the beginning (and should work) is actually equivalent to

   FILTER( ALL(METRICS[PNL]), METRICS[PNL]="REVENUE")

in fact the former gets translated into the latter internally by the DAX engine.

Anonymous
Not applicable

@AlB

 YOU ROCK! 

 

I also found a similar solution with this DAX, skipping METRIC lookup table entirely:

 

DIVIDE( [Actual PNL] , Calculate([Actual PNL], filter( ALLSELECTED(EIW), EIW[LPD Profit Loss Line]="CONSOLIDATED NET SALES (NG)")),0)

Anonymous
Not applicable

@AlB

Sorry I misunderstood. 

 

Actual PNL = sum(EIW[Actual]) 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.