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.
Hello,
I am trying to get a fixed count for accounts under a customer so that I can apply 1 of 2 billing models (measure A for customers with 1 account, measure B for customers with >1 account).
Table | Results I'm Trying to achieve | |||
Customer | Account | #ofAccounts | Measure to Use | |
1 | 1 | 2 | ---use measure B | |
1 | 2 | 2 | ---use measure B | |
2 | 1 | 1 | ---use measure A | |
3 | 1 | 1 | ---use meaure A | |
4 | 1 | 4 | ---use meaure B | |
4 | 2 | 4 | ---use meaure B | |
4 | 3 | 4 | ---use meaure B | |
4 | 4 | 4 | ---use meaure B | |
5 | 1 | 1 | ---use meaure A | |
6 | 1 | 2 | ---use meaure B | |
6 | 2 | 2 | ---use meaure B |
Problems:
Calculated Column Method:
If i create the #ofAccounts as a calculated column I am struggling to pull it into the billing measure:
Example: Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A]) --Problem I cant refence {#ofAccounts) because its not reference customer
Measure Method:
#ofAccounts:=CALCULATE(DISTINCTCOUNT(Data[AccountId]))
This works if just looking at the measure, if I filter on customer 1 it returns a value of 2. However, when i try to apply the billing measure --Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A])
it just reads off the account column and treats everything as a 1.
Thanks
Solved! Go to Solution.
Hi @Forrester,
Problems:
Calculated Column Method:
If i create the #ofAccounts as a calculated column I am struggling to pull it into the billing measure:
Example: Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A]) --Problem I cant refence {#ofAccounts) because its not reference customer
Maybe you could try this formula:
Billing=IF(MAX([#ofAccounts])>1,[Measure B],[Measure A])
Best regards,
Yuliana Gu
Hi @Forrester,
Problems:
Calculated Column Method:
If i create the #ofAccounts as a calculated column I am struggling to pull it into the billing measure:
Example: Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A]) --Problem I cant refence {#ofAccounts) because its not reference customer
Maybe you could try this formula:
Billing=IF(MAX([#ofAccounts])>1,[Measure B],[Measure A])
Best regards,
Yuliana Gu
Need source data sample/example. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |