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.
Dears,
I am a new POWER BI user and I am trying to figure the best way to do things currently done with Pivot tables and excel in general.
only basic knowledge of DAX language.
I have a huge amount of data to treat monthly for internal analysis, and I am trying to use POwer BI to optimize hours of manual job everytime I need to update something.
I made the below example to explain what I wish I could do.
I have my table on the left side with customers buying every month different amounts for different products.
I made a simple table on the left where customer transactions are repeated as they buy different products (I did not put the products in the table)
What I wish I could build in POWER BI, is a similar table (or visual graph) as the one on the right (see pic) where I could have
for each month, the customers counting split by value range.
The "customer counting" need to return the N# of distinct customer where their total in that given month is included in the fixed range.
Ideally, if I put the year or the month in a slicer or fiter, I could select the month I want and get the custmer counting (distinct) based on their total value in that given month.
I am not sure I well explained the wished result, but hope I can get some help.
Thanks in advance for anyone support.
Ciccio
Solved! Go to Solution.
Hi @CiccioST,
Add a calculated column in data table.
buying category = IF ( CALCULATE ( SUM ( 'Customer purchase'[Value] ), ALLEXCEPT ( 'Customer purchase', 'Customer purchase'[Customer Name], 'Customer purchase'[Month] ) ) < 300, "Buying<300", "Buying>300" )
Use a Matrix to display data, add [Customer Name] to "values" section, choose "Count(distinct)".
Best regards,
Yuliana Gu
Hi @CiccioST,
Add a calculated column in data table.
buying category = IF ( CALCULATE ( SUM ( 'Customer purchase'[Value] ), ALLEXCEPT ( 'Customer purchase', 'Customer purchase'[Customer Name], 'Customer purchase'[Month] ) ) < 300, "Buying<300", "Buying>300" )
Use a Matrix to display data, add [Customer Name] to "values" section, choose "Count(distinct)".
Best regards,
Yuliana Gu
HI Yuliana,
Just saw your reply on my post, thanks for your time and suggestion!
I will try this formula soon and keep you posted.
Cheers
FR
HI Yuliana,
The solution you proposed worked perfectly for me!
I can now change dynalically all filters in my dashboard and get the data sorted by category.
I have a very silly question now: If I want to add more categories into the value range, should I just copy and paste the formula starting from IF and just changing the range I want to get?
Thanks a lot!!
FR
I got it! It works by copying the whole formula from IF and changing the range I want to get.
Excellent!
Thanks a lot for your super support.
FR
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |