Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 :
PERIOD | PNL | VALUE |
May-18 | RENT | 100 |
May-18 | UTILITIES | 101 |
May-18 | SUPPLIES | 80 |
May-18 | REVENUE | 400 |
Jun-18 | RENT | 101 |
Jun-18 | UTILITIES | 100 |
Jun-18 | SUPPLIES | 101 |
Jun-18 | REVENUE | 450 |
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?
PNL | TOTAL | % OF REVENUE |
RENT | 201 | 23.6% |
UTILITIES | 201 | 23.6% |
SUPPLIES | 181 | 21.3% |
REVENUE | 850 | 100.0% |
Thank you in advance for your kind help!
Solved! Go to 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
the same data i need the following results in power pivot datamodal (DAX)
Row Labels | May | May % | Jun | Jun % | Grand Total |
RENT | 100 | 15% | 101 | 13% | 201 |
REVENUE | 400 | 59% | 450 | 60% | 850 |
SUPPLIES | 80 | 12% | 101 | 13% | 181 |
UTILITIES | 101 | 15% | 100 | 13% | 201 |
Total Expeneses | 681 | 100% | 752 | 100% | 1433 |
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" ) )
@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?
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
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
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
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.
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)
Sorry I misunderstood.
Actual PNL = sum(EIW[Actual])
EIW is fact table with the sample layout : period, metric (PNL), value (actual)....