Forum Discussion

Balaraju's avatar
Balaraju
Helper III
3 years ago
Solved

Need help in All, Allselected, AllExcept function

HI All,

I have data for distinct count of customer based on monthly level, my requirement is in bar chart I want to show the distinct count of cutomer by monthly level and quarter level.

So which dax I want to use for quarter level.

 

 

 

The expected output is in below screenshot:

 

Sample Data:

Year Month NumberDistinct count of customer by month level
2022-04513
2022-05549
2022-06319
2022-07481
2022-08451
2022-09351
2022-10543
2022-11508
2022-12355

 

Thanks,

Balaraju.

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

    quarterly customer count distinct: = 
CALCULATE (
    MAX(Data[Customer count]),
    ALL ( Data[Month end], Data[Year Month Number] ),
    VALUES ( Data[End of Quarter] )
)

2 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi,

    You need to define the type of the 'Year Month Number' column as a date and Format as a 'yyyy-mm', then develop the Line and Clustered Column chart and put the 'Year Month Number' in X-axis and 'Distinct of Month' in Y-axis and 'Distinct of Quarter' in Line Y-axis.

    an also adjust the legend place and change the color for bar and line chart :

    Thanks for Kudos and please mark it as a solution if it helps you.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

    quarterly customer count distinct: = 
CALCULATE (
    MAX(Data[Customer count]),
    ALL ( Data[Month end], Data[Year Month Number] ),
    VALUES ( Data[End of Quarter] )
)