About the DAX Calculations category


#1

This category is for all questions related to DAX calculations and formulas. DAX is a deep topic that is important to master in Power BI so within this category we’ll look to solve many common questions and problems users have.


#2

Halo Sam

So I desperately need help. Is it impossible to Calculate a static value in power bi? For example, lets say I have a value for each day and I calculate an average, everytime you place this average measure over dates on a line graph, it keeps on calculating the average for every single day, which just gives you the value for that day again.

It seems everytime you plot this over date it SUMS the values for that day divided by 1 day(that particular day). I would like to calulate a static measure, meaning if I plot this measure over date, it gives me a straight line, life time average. How on earth do you do this?? I have already tried, ALL, ALLEXCEPT,… ect. Filter functions only help for filters in the query, it does not help for slicers or plotting. To give you another example, I once tried to calulate a static total for carats, but carats have different sizes, so I wanted to calculate the total carats for the entire month, because the purpose was to calculate the % carats for every single class over total carats for the month, but if I plotted this over the carat classes it gave me 100% for all classes, meaning, what Power bi did was total carats for every single class divided by total for that class and again with this, not any filter function worked.

Other related Questions, how do I calculate standard deviation, so that I can show the average with the two standard deviation lines on the graph with those typical railway and also, power bi does not seem to have standard deviation? Your help will be much appreciated.

Thank you


#3

Well not completely static obviously, since it must change from period to period, but a life time average?


#4

Hi Sam,

I was doing one of your courses ‘solving analytical scenarios’ last night and you didnt explain really clearly on
the below fomula.

Customer Sales by Group =
CALCULATE( [Total Sales],
FILTER( VALUES( Customers[Customer Names] ),
COUNTROWS(
FILTER( ‘Customer Groups’,
RANKX( ALL( Customers[Customer Names] ), [Total Sales],DESC ) > ‘Customer Groups’[Min]
&& RANKX( ALL( Customers[Customer Names] ), [Total Sales], DESC ) <= ‘Customer Groups’[Max] ) )

0 ))

2 questions.

  1. Why does there need be ‘countrows >0’ ? does the fomula not work if i dont have the countrows function there?

  2. Why do i need all[customer names] in RANKX? if i remove all filters for Customers names by using ‘ALL’ fuction the total sales returns the same and i won’t be able to rank customers by ‘Sales Total’?

Appreciate your help.
Cheers