Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I already spoke with @OwenAuger and his solution from the topic http://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/200596#M88246. Because that topic was closed, I opened this topic. I want to use it like his example: https://www.dropbox.com/s/59cct4in6zqbxaj/Sales%20Top%20Other.pbix?
But function like Except doesn't work with Power BI desktop if you use a Tabular model. Also variables are not possible. Otherwise this could also be a solution: http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html
@OwenAuger came with a solution that I tried and this works:
Sales Amount Other Total Only =
IF ( NOT ( ISFILTERED ( Sales[Customer] ) ); [Sales Amount Other] )
I saw in your example that it has totals and every else is empty, but in my example everything is empty even the totals. I used your example: Sales Amount Other NO EXCEPT WITHOUT VARIABLES.
The formula works:
Uitval Top Overig = CALCULATE([Selected Top N Value];
KEEPFILTERS(
( FILTER (
ALL('ANG Fouten'[LPP_NAME]);
NOT (CONTAINS(
TOPN([Selected Top NNumber]; ALL('ANG Fouten'[LPP_NAME]);[Selected Top N Value]);'ANG Fouten'[LPP_NAME];'ANG Fouten'[LPP_NAME])
)
))))
But there is also a formula to get only the total of the other group:
Uitval Overig Totaal =
IF (( NOT( ISFILTERED ( 'ANG Fouten'[LPP_NAME] ) );[Uitval Top Overig]))
And that formula doesn't work / gives a empty result. @OwenAuger Can you help me with this?
Or has somebody else an other solution?
Regards, Hilbert
Hi @Hspuybroek,
In order to change the Expect function with some other possible methods, please replace the highlighted part with below formula:
Sales Amount Other = CALCULATE ( [Sales Amount], KEEPFILTERS ( TOPN (COUNTROWS(Sales)- [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount],ASC ) ) )
Please check whether above advice works in your scenario.
Regards,
Yuliana Gu
Thank you, like the solution that i mentioned this will work. But I want only the total and that will not work combining with in my case the LPP name see screen above.
But after adding the LPP name I don't see my other category back. That is what i want.
To get the total of the other category i did the following:
Uitval Overig Totaal =
IF (( NOT( ISFILTERED ( 'ANG Fouten'[LPP_NAME] ) );[Sales Amount Other]))
And then later:
Uitval Top & Overig =
IF (
HASONEVALUE ( 'Top en Overig'[Top_soort]);
SWITCH (
VALUES ( 'Top en Overig'[Top_soort] );
"Top"; [Uitval Top];
"Overig";[Uitval Overig Totaal]
);
[Selected Top N Value]
)
But the Catergory Overig is empty because I don't get any total in the Uitval Overig Totaal measure. Do you have any solution?
Thank you!
Regards, Hilbert
Hi @Hspuybroek
I haven't yet had a chance to re-look at this one - will read through this thread properly and get back to you.
Regards,
Owen
Sorry for delay in replying - have read your question and would be easier to answer with a sample model to figure out what is going on.
Do you have one you can upload?
I was on vacation, that is the reason for my late response. Here is the link to the dashboard: https://www.dropbox.com/s/oz3p97x2niv9caf/ANG%20Dashboard.pbix?dl=0
The folder formules contains the calculation that i have used. I hope that you can see something with a tabular connection as a source.
Regards, Hilbert
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |