Forum Discussion
Count a dynamic measure
Hi, I'm trying to calculate a dinstinct count of rows for a specific measure and couldn't find a way to do this. The scenario: I have a table with customers, quarters, quarter aging, revenue an...
Anonymous
this is what the data looks like:
| Customer ID | Customer Name | Region | Product | Quarter | Quarter Aging | Quarter Week Numer | Orders Revenue | Orders Margin |
| 2134590 | ABB | US | ZZ | 2020-Q2 | -4 | 1 | $1,000,000.00 | $500,000.00 |
| 1235857 | ACC | BR | ZZ | 2020-Q3 | -3 | 2 | $950,000.00 | $380,000.00 |
| 9023411 | ADD | MX | XX | 2020-Q1 | -5 | 3 | $850,000.00 | $297,500.00 |
| 8923452 | AEE | AR | XX | 2020-Q4 | -2 | 3 | $789,200.00 | $276,220.00 |
| 4390563 | AFF | CL | ZZ | 2019-Q2 | -8 | 2 | $589,000.00 | $235,600.00 |
| 2348595 | EGG | COL | CC | 2020-Q3 | -3 | 7 | $120,350.00 | $60,175.00 |
| 1010293 | FFG | AR | VV | 2020-Q3 | -3 | 8 | $1,000,500.00 | $300,150.00 |
| 1928350 | BBB | BR | VV | 2019-Q2 | -8 | 10 | $700,589.00 | $280,235.60 |
| 2348950 | EDD | PR | CC | 2020-Q1 | -1 | 11 | $123,989.00 | $61,994.50 |
| 2134590 | ABB | US | ZZ | 2021-Q2 | 0 | 10 | $4,398,123.00 | $1,099,530.75 |
| 1235857 | ACC | BR | ZZ | 2021-Q1 | -1 | 13 | $3,895,040.00 | $1,129,561.60 |
| 9023411 | ADD | MX | XX | 2021-Q2 | 0 | 5 | $89,034.00 | $44,517.00 |
| 8923452 | AEE | AR | XX | 2021-Q1 | -1 | 6 | $120,930.00 | $60,465.00 |
| 4390563 | AFF | CL | ZZ | 2021-Q1 | -1 | 5 | $590,320.00 | $177,096.00 |
Measures created:
- Total revenue: removing any quarter selected - CALCULATE(SUM(Actual[Orders Revenue]),ALL('Quarter'[Quarter]))
- LY Revenue: conditional based on the quarter selected - nested IF:
LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",
CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-4"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",
CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-5"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",
CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-6").... and goes for -13 Quarter Aging.
- Running total Revenue QTD and LY - this is to sum the revenue as the weeks of the quarter goes by to give cummulative revenue
RTotal Rev =
CALCULATE (
SUM ( Actual[Orders Revenue] ),
FILTER ( ALLSELECTED('Quarter'),
'Quarter'[Quarter Week Num] <= MAX ( 'Quarter'[Quarter Week Num] )
))
- Y/Y Rev and margin comparison
Y/Y Rev =
(ROUNDUP([RTotal Rev],3) / [RTotal LY Rev]) -1
- And measures for the Account classification
Quadrant I = IF(AND([Y/Y Rev]>=0,[Y/Y GM]>=0),"I",BLANK())
Quadrant II = IF(AND([Y/Y Rev]<0,[Y/Y GM]>=0),"II",BLANK())
Quadrant III = IF(AND([Y/Y Rev]<0,[Y/Y GM]<=0),"III",BLANK())
Quadrant IV = IF(AND([Y/Y Rev]>0,[Y/Y GM]<=0),"IV",BLANK())
Situation
If I select a TABLE View and put Account name and quadrant, I can have the list of the accounts that are being classified within that, but I don't have a way to count how many accounts are those.
For each time that I select a quarter, all the measures calculate the LY and Y/Y and give a different classification, as you can see in the formulas.
Outcome expected: be able to quantify how many accounts are increasing and/or decreasing Y/Y depending on the quarter selected.
See if this is helpful.
Thank you
Hi NETO13 ,
So what's in measure [RTotal LY Rev] and [Y/Y GM]?
Based on my understanding,you'd better create a new table using below dax expression:
Table 2 = DISTINCT('Table'[Quarter])
It will be used in slicer.
Then modify your measures for Total revenue,LY Rev and RTotal Rev as below:
Total revenue = CALCULATE(SUM('Quarter'[Orders Revenue]),FILTER(ALL('Quarter'),'Quarter'[Quarter]<>MAX('Table 2'[Quarter])))RTotal Rev =
CALCULATE (
SUM ( 'Quarter'[Orders Revenue]),
FILTER (ALL('Quarter'),'Quarter'[Quarter]=SELECTEDVALUE('Table 2'[Quarter])&&'Quarter'[Quarter Week Numer]<=MAX('Quarter'[Quarter Week Numer])))LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-4"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-5"),
IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",
CALCULATE('Quarter'[Total revenue],'Quarter'[Quarter Aging]="-6"),BLANK())))
Try if the above measures would work.
Best Regards,
Kelly
Kelly
Did I answer your question? Mark my post as a solution!