Hi ,
Average Products Per Q = AVERAGEX(
VALUES( Calendar[Quarter & Year] ),
[Total Products] )
and
Average Product All Q =
CALCULATE( [Average Products Per Q], ALL( Calendar ))
what should happen when i choose specific Quarter , the all formula amount should not becasue of All but that didn’t happen and it change
what could be the reason i checked my model i didn’t find a problem in relationship
Thanks,
Abeer
Hi @Beer,
I have been testing with an example and when I remove the filter context with ALL (Dim_Dates) the calculation does it well.
Avg. Quantity per Q = AVERAGEX( VALUES( Dim_Dates[Quarter & Year] ) , [Total Quantity] )
Avg. ALL Quantity per Q = CALCULATE( [Avg. Quantity per Q], ALL(Dim_Dates) )
Instead, if I use ALL(Dim_Dates[Quarter & Year]) instead of ALL(Dim_Dates), it doesn’t work well because we haven’t really removed this field from the filter context since I have it sorted by the numeric field Dim_Dates[Quarter & Year Sort]. To remove it completely we need to remove both fields:
Avg ALL Quantity per Q = CALCULATE( [Avg. Quantity per Q], ALL(Dim_Dates[Quarter & Year]), ALL(Dim_Dates[Quarter & Year Sort]) )
If you can share the example of the pbix file you are testing on it would be a great help.
This is my example.
Test_JAFP.pbix (480.3 KB)
Greetings,
Hi jafernandezpuga,
Thanks for your reply , find attached the file i am working on.
European Sales Organisation …pbix (473.1 KB)
I also attach the file of the course of Sam which the all work as it suppose to do Detect Abnormal Patterns In Behaviour Using DAX In Power BI
Detect Abnormal Patterns In Behaviour Using DAX In Power BI.pbix (471.9 KB)
Abeer
European Sales Organisation …pbix (473.1 KB)
Hi @Abeer,
If you mean the measure:
Average Product All Q = CALCULATE( [Average Products Per Q], ALL(Calender) )
which you are using in the table by Customers[Customers Names]
of the Abnormal Purchasing Batterns tab, as this table has a filter context of Customers[Customers Names], we have to ignore it in the calculation of the measure.
Average Product All Q = CALCULATE( [Average Products Per Q], ALL(Calender), ALL( Customers[Customer Names]) )
Greetings,
Hi jafernandezpuga,
If you see the other file i sent which is detect abnormal patterns,
you will find that Sam Macy made it without add All(customers name) to the formula and it is working , can you check it Pls. and see, that what confuse me since yesterday.
Thanks
Abeer
Hi @Abeer,
In the Detect Abnormal Patterns In Behavior Using DAX In Power BI file in the measure:
Avg. Products Bought in Quarter =
AVERAGEX(
VALUES( Dates[Quarter & Year] ),
[Unique Products Bought] )
You are calculating the average of the different products sold for each customer taking into account the filters of the Dates table.
In the second:
Avg. Products Bought All Quarters = CALCULATE( [Avg. Products Bought in Quarter], ALL( Dates ) )
It performs the same calculation for each client but without taking into account the filters of the Dates table.
In the third:
Avg. Products Bought All Quarters And ALL Customers = CALCULATE( [Avg. Products Bought in Quarter], ALL( Dates ) , ALL(Customers[Customer Name] ) )
Calculates the average of the different products sold for all dates and customers.
In the Calculations tab I did the same measurements for Sum of Quantity to see it more clearly.
Greetings,
Detect Abnormal Patterns In Behaviour Using DAX In Power BI_JAFP.pbix (454.0 KB)
Hi @Abeer we’ve noticed that no response has been received from you since a few days ago.
We just want to check if you still need further help with this post?
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Abeer, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
Hi ,
Sorry but i had some internet problem last few days.
Thanks,
Abeer
Hi
Thanks for your reply ,
i had some internet problem last few days. but the problem have been solved.
Thanks,
Abeer