Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a table of customer Buys and Sells. I calculate the Daily Ownership Change by subtracting these two.
Also, I have a measure to check if Daily Ownership Change was under threshold (25%).
More specifically, from this table
Date | Customer | Type | Items | ||
1/1/2021 | A | Buy | 100 | ||
1/1/2021 | A | Buy | 100 | ||
1/1/2021 | A | Sell | 500 | ||
1/1/2021 | A | Buy | 300 | ||
8/1/2021 | B | Buy | 100 | ||
8/1/2021 | B | Sell | 50 | ||
9/1/2021 | A | Sell | 100 | ||
9/1/2021 | A | Buy | 80 |
I then create the table visual
Date | Customer | Daily Ownership Change | Daily Ownership Change PCT | isUnchagned | ||||
1/1/2021 | A | 0 (ABS(100 + 100 -500 + 300)) | 0% (divide (Daily Ownership Change, TotalItems) -> 0/1000 | 1 (if Daily Ownership Change PCT < 0.25, 1, 0) | ||||
8/1/2021 | B | 50 | 33.3% (50/150) | 0 | ||||
9/1/2021 | A | 20 |
| 11% (20/180) | 1 |
What I want now is to create a Period Report showing how many days the isUnchanged was 1,
Lets say for 1/1/2021 - 10/1/2021
Customer | isUnchanged Total | |
A | 2 | |
B | 0 |
When I create the latter visual, I get zero isUnchanged Total for all customers.
Any ideas??
Thank you in advance,
Kyriakos
Solved! Go to Solution.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create measure.
Daily Ownership Change =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Buy"))
VAR _2=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Sell"))
return
ABS(_1-_2)
Daily Ownership Change PCT =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
return
DIVIDE('Table'[Daily Ownership Change],_1)
isUnchagned =
IF('Table'[Daily Ownership Change PCT]<0.25,1,0)
Result:
2. Put only the result of [Customer]:
This is because Measure will only perform calculations based on the fields you place. There is only [Customer] in this table, and calculations will be grouped by [Customer] instead of [Date].
3. You can use measure to create an identical virtual table.
isUnchanged Total =
var _1=
SUMMARIZE('Table','Table'[Date],'Table'[Customer]," Daily Ownership Change",[Daily Ownership Change],"2",[Daily Ownership Change PCT],"3",[isUnchagned])
var _2=COUNTAX(FILTER(_1,[3]=1),[Customer])
return
IF(_2=0,0,_2)
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create measure.
Daily Ownership Change =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Buy"))
VAR _2=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Type]="Sell"))
return
ABS(_1-_2)
Daily Ownership Change PCT =
var _1=CALCULATE(SUM('Table'[Items]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
return
DIVIDE('Table'[Daily Ownership Change],_1)
isUnchagned =
IF('Table'[Daily Ownership Change PCT]<0.25,1,0)
Result:
2. Put only the result of [Customer]:
This is because Measure will only perform calculations based on the fields you place. There is only [Customer] in this table, and calculations will be grouped by [Customer] instead of [Date].
3. You can use measure to create an identical virtual table.
isUnchanged Total =
var _1=
SUMMARIZE('Table','Table'[Date],'Table'[Customer]," Daily Ownership Change",[Daily Ownership Change],"2",[Daily Ownership Change PCT],"3",[isUnchagned])
var _2=COUNTAX(FILTER(_1,[3]=1),[Customer])
return
IF(_2=0,0,_2)
4. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
[Total IsUnchanged] = SUM( 'Table'[IsUnchanged] )
What I'm saying above is that you could (maybe even should) create the second table (you call it visual) in Power Query in full, meaning for all customers and all dates. If you do, then it'll be easier and MUCH faster to calculate other things. Basically, the rule is this: You should pre-calculate everything that you can in Power Query. Calculated columns should be calculated in Power Query unless there is absolutely no other way than to do this with DAX.
There are many reasons behind the above but to explain all this would take a full article or a chapter in a book.
@Anonymous
The first calculation should be performed in Power Query (by adding the 3 columns). Then, back in Power BI Desktop you can easily create the measure by just summing up the values in the last column.
By the way, you should create a measure for the Total, not use the automatic summation feature of PBI Desktop. Do you have such a measure? What does it look like?
@Anonymous thanks for your response.
I didn't quite get the first part where you mention Power Query.
I have the following measures:
Buys = CALCULATE(SUM(Items), 'Table'[Type] = "Buy"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |