Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi Team,
Here is my data , i would like to create a measure which should calcualte no of uses who have purchased more than 1 prouduct dividede by total numbe of uses for the selected month
IF i choose multiple months am expecting the same behaviour for the selectd months [Total uses who bought more than 1 proudct / Total no of uses for selcted moths]
Sample data with filters
DATA TABLE REPORT SLICER
Solved! Go to Solution.
Hi @BalaVenuGopal ,
You will need to create a calculated table as slicer.
Table 2 = DISTINCT('Table'[MONTH])
Check the following measures.
Measure =
var a = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT]),FILTER(ALLEXCEPT('Table','Table'[USER],'Table'[SUB DT]),'Table'[MONTH] in VALUES('Table 2'[MONTH])))
return
MAXX(ALLEXCEPT('Table','Table'[USER]),a)
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table',[Measure]>1))/CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table','Table'[MONTH] in VALUES('Table 2'[MONTH])))
Result would be shown as below.
Best Regrads,
Jay
Hi @BalaVenuGopal ,
You will need to create a calculated table as slicer.
Table 2 = DISTINCT('Table'[MONTH])
Check the following measures.
Measure =
var a = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT]),FILTER(ALLEXCEPT('Table','Table'[USER],'Table'[SUB DT]),'Table'[MONTH] in VALUES('Table 2'[MONTH])))
return
MAXX(ALLEXCEPT('Table','Table'[USER]),a)
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table',[Measure]>1))/CALCULATE(DISTINCTCOUNT('Table'[USER]),FILTER('Table','Table'[MONTH] in VALUES('Table 2'[MONTH])))
Result would be shown as below.
Best Regrads,
Jay
If im understanding you correctly, from your example you're looking for the result 1/7? (14%)
If this is the case the following measure should work:
Measure =
DIVIDE(
CALCUlATE( COUNTROWS( SUMMARIZE ( Table , [Users] , "Count of products" , COUNT(Table[Products])) , [Count of products] <> 1 ) ,
COUNTROWS( SUMMARIZE ( Table , [Users] , "Count of products" , COUNT(Table[Products])) , BLANK())
Br,
J
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |