Create a custom indicator using DAX on power BI

Question

FOR ALL POWER BI USERS

I have created a table visual name from table "Example" given below as raw data (similar to result table except Ind column). I want to create an indicator which will be based on the column total using DAX.

Company | Rev 2018      | Rev 2019       | Rev YoY(%) | 
-----------------------------------------------------------
A       |   440,980,812 |   321,015,626 | -27.20%     | 
B       |   587,171,150 |   248,150,205 | -57.74%     | 
C       |   693,692,632 |   255,633,145 | -63.15%     | 
D       |   753,951,313 |   266,033,862 | -64.71%     | 
E       |   387,652,076 |   393,439,270 |   1.49%     | 
Total   | 2,863,447,983 | 1,484,272,108 | -48.16%     |

My current measure calculation is given below

Rev 2018 = CALCULATE(sum(Example[Rev]),Example[Year]=2018)
Rev 2019 = CALCULATE(sum(Example[Rev]),Example[Year]=2019)
Rev YoY(%) = ([Rev 2019]-[Rev 2018])/[Rev 2018]

I want to create an indicator (Ind) which will show 1 if Rev YoY(%)(-27.20% for company A) for the company is greater than equal to (>=) overall/total Rev YoY(%)(-48.16% of total), else it will show 0. And it will changes based on slicer selections(if Jan is selected, the updated values should changes based on Jan and similarly for other selections such as Feb , Mar etc).

As of now, the value is based on YTD values.Based on the Jan , Feb etc month , the values will be updated for revenue and YoY, simultaneously Indicator measure should also get updated.

Final result will look like below

Company | Rev 2018      | Rev 2019       | Rev YoY(%) | Ind
-----------------------------------------------------------
A       |   440,980,812 |   321,015,626 | -27.20%     | 1
B       |   587,171,150 |   248,150,205 | -57.74%     | 0
C       |   693,692,632 |   255,633,145 | -63.15%     | 0
D       |   753,951,313 |   266,033,862 | -64.71%     | 0
E       |   387,652,076 |   393,439,270 |   1.49%     | 1
Total   | 2,863,447,983 | 1,484,272,108 | -48.16%     | 1

Please help in case you have the solution.

I tried to used filer(allcrossfiler) but it makes the field constant which does not change with slicers

Rev_total% = 
CALCULATE(
    [Rev YoY(%)],
    ALLCROSSFILTERED(Example)
)

Base Raw data

Company Year    Month   Rev
A   2018    Jan 3715518
A   2018    Feb 62195456
A   2018    Mar 47896563
A   2018    Apr 30397293
A   2018    May 13316124
A   2018    Jun 54702783
A   2018    Jul 23559246
A   2018    Aug 56357008
A   2018    Sep 91266366
A   2018    Oct 7826397
A   2018    Nov 30081453
A   2018    Dec 19666605
A   2019    Jan 20525691
A   2019    Feb 55636582
A   2019    Mar 70832178
A   2019    Apr 51101460
A   2019    May 71658353
A   2019    Jun 51261362
B   2018    Jan 70866878
B   2018    Feb 16605125
B   2018    Mar 77399457
B   2018    Apr 93675100
B   2018    May 24187836
B   2018    Jun 17141132
B   2018    Jul 23189326
B   2018    Aug 1228527
B   2018    Sep 77025448
B   2018    Oct 69069603
B   2018    Nov 61201073
B   2018    Dec 55581645
B   2019    Jan 49529171
B   2019    Feb 30268530
B   2019    Mar 58895051
B   2019    Apr 16378441
B   2019    May 63289350
B   2019    Jun 29789662
C   2018    Jan 28386565
C   2018    Feb 55081195
C   2018    Mar 98650639
C   2018    Apr 13600972
C   2018    May 79286377
C   2018    Jun 97910757
C   2018    Jul 59601906
C   2018    Aug 60499979
C   2018    Sep 10555754
C   2018    Oct 21239252
C   2018    Nov 79278588
C   2018    Dec 89600648
C   2019    Jan 27489712
C   2019    Feb 8085774
C   2019    Mar 33489287
C   2019    Apr 52598275
C   2019    May 50816690
C   2019    Jun 83153407
D   2018    Jan 69955023
D   2018    Feb 1684049
D   2018    Mar 44503967
D   2018    Apr 91505045
D   2018    May 74480545
D   2018    Jun 70038948
D   2018    Jul 28811752
D   2018    Aug 82052925
D   2018    Sep 97215945
D   2018    Oct 48093159
D   2018    Nov 96939697
D   2018    Dec 48670258
D   2019    Jan 68414609
D   2019    Feb 34593576
D   2019    Mar 28277668
D   2019    Apr 46146140
D   2019    May 83794133
D   2019    Jun 4807736
E   2018    Jan 21180873
E   2018    Feb 14552267
E   2018    Mar 27409537
E   2018    Apr 68894164
E   2018    May 24608038
E   2018    Jun 12774844
E   2018    Jul 13193433
E   2018    Aug 89921780
E   2018    Sep 34581806
E   2018    Oct 52068148
E   2018    Nov 11374013
E   2018    Dec 17093173
E   2019    Jan 21748970
E   2019    Feb 95983245
E   2019    Mar 49661560
E   2019    Apr 90056699
E   2019    May 72277971
E   2019    Jun 63710825

Can you please complete your question? Your Example table is missing, your result is very baddly formatted so it is hard to read. You talk about a slicer which should do something but it isnot clear what it should do. — Aldert
Apologies for the format, Example table is same as results table except Indicator column. As of now, the value is based on YTD values.Based on the Jan , Feb etc month , the values will be updated for revenue and YoY, simultaneously Indicator measure should also get updated. Hope it is more clear. — User_powerBI
You are saying you are using measures, this indicates that you do not have a table but only a visual. I tried your case and added a column to a table: Indicator = Revenue[ Rev YoY(%) ] > AVERAGE(Revenue[ Rev YoY(%) ]). This gave me true/false accordingly — Aldert
I am trying the same on my dashboard using your method, Indicator = Revenue[ Rev YoY(%) ] > AVERAGE(Revenue[ Rev YoY(%) ]). Average is looking for column name and Revenue[ Rev YoY(%) ] is a measure. In your case average is accepting Revenue[ Rev YoY(%) ] as arguement? My case it is not. — User_powerBI
Yes, this is logical becuase you are using measures, it does ot have a reference to the row. I start to understand that you are looking for. Can you give me your base data table and some rows of data, than I can better support you. — Aldert

Aldert Aldert · Accepted Answer · 2019-08-16T07:09:02

You can use the fllowing:

Indicator = if(Example[Rev YoY(%)] > CALCULATE(Example[Rev YoY(%)];ALL(Example[Company]));1;0)

The ALL is doing the trick, it tells pickup all companies data but still keep all other filters.

Some advice:

I would work with real dates and combine the year and month column, this makes it much easier to work with future data.

You are having now 2018 and 2019, what if your data grows? more years to come.. It is better to talk about PrevYear/NextYear. What you can do is add a column to your data RevNextYear, based on this your reports will always work:

RevNextYear = CALCULATE(sum(RawRevenue[Rev]);
    FILTER(RawRevenue;RawRevenue[Company] = EARLIER(RawRevenue[Company]) &&
    RawRevenue[Month] = EARLIER(RawRevenue[Month]) && 
    RawRevenue[Year] =  EARLIER(RawRevenue[Year]) + 1)
    )

Create a custom indicator using DAX on power BI

1 Answers