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.
Hi all,
I have a matrix on which I am showing the total count of each value occurences for each row and colu + the count of distinct values.
My goal is to get in the matrix only the values for which the total of the row for distinct values is > 1.
What I have now:
What I would like to have
Is this manageable? How could I filter the total for total of distinct values by row >0 ?
Thank you all in advance for your help
Solved! Go to Solution.
HI @Mbechet,
I modified your formula and it seems work on my side.
Custom = var currentAgent=LASTNONBLANK(Sheet1[Agent],[Agent]) return if(COUNT(Sheet1[Amount])=COUNTX(FILTER(ALL(Sheet1),[Agent]=currentAgent),[Amount])||COUNT(Sheet1[Amount])=COUNTX(ALL(Sheet1),[Amount]),DISTINCTCOUNT(Sheet1[Amount]),COUNT(Sheet1[Amount]))
Notice: if statement check agent group to filter on total row, so if your agent has only one amount category, distinct count formula will also calculate on that row.
Regards,
Xiaoxin Sheng
HI @Mbechet,
For your scenario, I think you can modify your formula to add a conditional to filter the calculation on total row.
Sample: add condition to check column group.
Custom = var currentItem=LASTNONBLANK('Sample Table'[Date].[Year],[Date].[Year]) return IF(COUNTROWS('Sample Table')=COUNTROWS(FILTER(ALL('Sample Table'),[Date].[Year]=currentItem)),"Total Row Formula",SUM([Amount]))
Regards,
Xiaoxin Sheng
Dear @v-shex-msft,
Thank you for your reply.
I have tried your proposal but I am not able to get the expected result (certainly due to a lack of knowledge...).
To explain my need in a clearer way I have made a demo PBIX file
I think the problem that I have compare to your example is that I am using different fields for my rows and columns.
In brief, my project and goal looks as below
My final goal is to be able to find out if I have more that 1 disctinct amount for each person (what I would see in the total of rows) and the see the share by column of the count of each values for each project.
Do you have an idea how to do it?
Thank you again for your help.
Regards
Hi @Mbechet,
I think I have clear your requirement, normal column show total amount, and at total column switch to distinct mode, right?
It seems like I can't download for your sharing link, can you please upload this file to onedrive?
Sample:
Regards,
Xiaoxin Sheng
Hello @v-shex-msft
Yes you got it right but I am not able to figure out how to apply it to my model.
You can download the files here.
Thanks
HI @Mbechet,
I modified your formula and it seems work on my side.
Custom = var currentAgent=LASTNONBLANK(Sheet1[Agent],[Agent]) return if(COUNT(Sheet1[Amount])=COUNTX(FILTER(ALL(Sheet1),[Agent]=currentAgent),[Amount])||COUNT(Sheet1[Amount])=COUNTX(ALL(Sheet1),[Amount]),DISTINCTCOUNT(Sheet1[Amount]),COUNT(Sheet1[Amount]))
Notice: if statement check agent group to filter on total row, so if your agent has only one amount category, distinct count formula will also calculate on that row.
Regards,
Xiaoxin Sheng
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |