Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a dataset that looks a bit like this
For Deal Size Min and Max, it is in the dataset itself which is an excel sheet. The Average deal size is a measure that I have created. This is the DAX i wrote to get the Average Deal Size.
Average Deal Size test = CALCULATE(AVERAGE('PE Direct DP Form'[Deal Size (Max)]) + AVERAGE('PE Direct DP Form'[Deal Size (Min)]))/2
As you can see from the above picture, the total average is taking into account those where the average deal size is 0. This then skews the average. Is there a way to modify this measure to not take instances where the average deal size is 0 or blank?
Thanks
Solved! Go to Solution.
Hi @rjsidek ,
The purpose we use maxdate because we want to get the latest record for the company, we should add both date and company name into the filter, could you please try to use the following measure?
Average Deal Size test =
VAR latestd =
CALCULATE (
MAX ( Table1[Date of Event] ),
FILTER ( Table1, Table1[Deal Size (Max)] <> 0 && Table1[Deal Size (Min)] <> 0 )
)
VAR cname =
CALCULATE ( MAX ( Table1[Company Name] ), Table1[Date of Event] = latestd )
RETURN
CALCULATE (
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Deal Size (Max)] ),
Table1[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( Table1[Deal Size (Min)] ),
Table1[Deal Size (Min)] + 0 <> 0
),
2
),
'Table1'[Company Name] = cname && 'Table1'[[Date of Event] = latestd
)
Best regards,
would try a Filter :
Column = CALCULATE(AVERAGE('Table (2)'[number]);FILTER('Table (2)';'Table (2)'[number]>0))
you can also use filter in same wain in a messure
Hi @rjsidek ,
We can try to use the following measure to meet your requiremnet:
Average Deal Size test =
(
CALCULATE (
AVERAGE ( 'PE Direct DP Form'[Deal Size (Max)] ),
'PE Direct DP Form'[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( 'PE Direct DP Form'[Deal Size (Min)] ),
'PE Direct DP Form'[Deal Size (Min)] + 0 <> 0
)
) / 2
Best regards,
Hi, thanks for your reply. The formula almost works, but is not exactly what I want it to be. I was wondering if you could help me figure it out.
Using the DAX you suggested, this is the result:
It is ignoring 0s which is great! But, If i were to put this in a card visualization, it would return the value 57.5. This is because it is taking an average of the 2 average entries from the above 2 rows. This is not what I want. I want it to show for only the latest entry. Thus, the desired output is for it to only show the second row, where the deal size max and deal size min is 100 and 50 respectively. This is because that is the latest entry for the 2 deal size columns. The desired output for average would be 75.
How would i edit the code to get it working that way?
Thank you so much for your help
Hi @rjsidek ,
We can try to use the following measures to meet your requirement:
Average Deal Size test =
VAR latestd =
MAX ( 'PE Direct DP Form'[Date] )
VAR cname =
CALCULATE (
MAX ( 'PE Direct DP Form'[Company Name] ),
FILTER (
'PE Direct DP Form',
'PE Direct DP Form'[Date] = latestd
&& 'PE Direct DP Form'[Deal Size (Max)] <> 0
&& 'PE Direct DP Form'[Deal Size (Min)] <> 0
)
)
RETURN
CALCULATE (
DIVIDE (
CALCULATE (
AVERAGE ( 'PE Direct DP Form'[Deal Size (Max)] ),
'PE Direct DP Form'[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( 'PE Direct DP Form'[Deal Size (Min)] ),
'PE Direct DP Form'[Deal Size (Min)] + 0 <> 0
),
2
),
'PE Direct DP Form'[Company Name] = cname
)
Best regards,
This is the code I used, basically same as your code just the parameters were tweaked to fit my dataset
Average Deal Size test =
VAR latestd =
MAX ( Table1[Date of Event] )
VAR cname =
CALCULATE (
MAX (Table1[Company Name] ),
FILTER (
Table1,
Table1[Date of Event] = latestd
&& Table1[Deal Size (Max)] <> 0
&& Table1[Deal Size (Min)] <> 0
)
)
RETURN
CALCULATE (
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Deal Size (Max)] ),
Table1[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( Table1[Deal Size (Min)] ),
Table1[Deal Size (Min)] + 0 <> 0
),
2
),
'Table1'[Company Name] = cname
)
And this is a screenshot of pbi after applying this DAX
For some reason, now the total is gone. The end goal is to show the value in a card visualization, which is now just showing up as a blank. Any ideas how to rectify this?
Hi @rjsidek ,
Sorry for our mistake, could you please try to use the following formula?
Average Deal Size test =
VAR latestd =
CALCULATE (
MAX ( Table1[Date of Event] ),
FILTER ( Table1, Table1[Deal Size (Max)] <> 0 && Table1[Deal Size (Min)] <> 0 )
)
VAR cname =
CALCULATE ( MAX ( Table1[Company Name] ), Table1[Date of Event] = latestd )
RETURN
CALCULATE (
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Deal Size (Max)] ),
Table1[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( Table1[Deal Size (Min)] ),
Table1[Deal Size (Min)] + 0 <> 0
),
2
),
'Table1'[Company Name] = cname
)
Best regards,
Hi @v-lid-msft
So now, it is showing the average, but the value it shows is an average of all the previous entries
Not sure why this would be the case when the measure is asking it to return based on Max Date. Is there something else that I could try? The number I want in the card visualization would be only the latest one, so the desired output would be 75
Hi @rjsidek ,
The purpose we use maxdate because we want to get the latest record for the company, we should add both date and company name into the filter, could you please try to use the following measure?
Average Deal Size test =
VAR latestd =
CALCULATE (
MAX ( Table1[Date of Event] ),
FILTER ( Table1, Table1[Deal Size (Max)] <> 0 && Table1[Deal Size (Min)] <> 0 )
)
VAR cname =
CALCULATE ( MAX ( Table1[Company Name] ), Table1[Date of Event] = latestd )
RETURN
CALCULATE (
DIVIDE (
CALCULATE (
AVERAGE ( Table1[Deal Size (Max)] ),
Table1[Deal Size (Max)] + 0 <> 0
)
+ CALCULATE (
AVERAGE ( Table1[Deal Size (Min)] ),
Table1[Deal Size (Min)] + 0 <> 0
),
2
),
'Table1'[Company Name] = cname && 'Table1'[[Date of Event] = latestd
)
Best regards,
Try like
Average Deal Size test = CALCULATE(divide(sum('PE Direct DP Form'[Deal Size (Max)]),count('PE Direct DP Form'[Deal Size (Max)]))
+ divide(sum('PE Direct DP Form'[Deal Size (Min)]),count('PE Direct DP Form'[Deal Size (Min)])))/2
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @amitchandak
Thanks for replying.
The issue still persists. The total average at the bottom is still taking into account those entries where the Average deal size is 0. This then skews the data where the total is divided by the count that includes entries which are 0.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |