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,
In Power BI I do have a visualization like this
Column from Date Table | Column from Sales Table | Column from Product Table | Column from Sales Table | Column from Sales Table |
Date | Product | Category | Partner ID | Revenue |
January | A1 | A | X | 2323 |
January | B1 | B | Y | 24342 |
January | B1 | D | X | 343443 |
January | C1 | B | X | 3434 |
January | B1 | B | Y | 34343 |
February | C1 | C | Y | 3434 |
February | D3 | D | Y | 343 |
February | A1 | A | X | 4234 |
February | B1 | B | X | 34343 |
February | C2 | C | X | 3434 |
And I would like to create a measure "#Average per Active Partner" that returns the average number or Categories sold per ACTIVE Partner. ACTIVE Partner = Revenue >0
Date | # Average per Active Partner |
January | 1.5 |
February | 2.5 |
Could you kindly advise on the DAX formula to use in this case ?
Thanks a lot for your patience and support.
Solved! Go to Solution.
Try this measure
#ActivePartners = VAR _countPartnerID = DISTINCTCOUNT( SalesTable[Partner ID] ) VAR _countCategory = DISTINCTCOUNT( SalesTable[Category] ) RETURN CALCULATE( DIVIDE( _countCategory ; _countPartnerID ) ; FILTER( SalesTable ; SalesTable[Revenue] > 0 ) )
Hi, try with this measure:
Average per Partner = VAR DistinctCategories = CALCULATE ( DISTINCTCOUNT ( 'Product Table'[Category] ); FILTER ( SalesTable; SalesTable[Revenue] > 0 ) ) VAR ActivePartners = CALCULATE ( DISTINCTCOUNT ( SalesTable[Partner ID] ); FILTER ( SalesTable; SalesTable[Revenue] > 0 ) ) RETURN DIVIDE ( DistinctCategories; ActivePartners )
Regards
Victor
Lima - Peru
Hi, try with this measure:
Average per Partner = VAR DistinctCategories = CALCULATE ( DISTINCTCOUNT ( 'Product Table'[Category] ); FILTER ( SalesTable; SalesTable[Revenue] > 0 ) ) VAR ActivePartners = CALCULATE ( DISTINCTCOUNT ( SalesTable[Partner ID] ); FILTER ( SalesTable; SalesTable[Revenue] > 0 ) ) RETURN DIVIDE ( DistinctCategories; ActivePartners )
Regards
Victor
Lima - Peru
Try this measure
#ActivePartners = VAR _countPartnerID = DISTINCTCOUNT( SalesTable[Partner ID] ) VAR _countCategory = DISTINCTCOUNT( SalesTable[Category] ) RETURN CALCULATE( DIVIDE( _countCategory ; _countPartnerID ) ; FILTER( SalesTable ; SalesTable[Revenue] > 0 ) )
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |