Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Task:
To show top 5 products out of 100s, user can select a period with a slicer.
So I create a table using the following DAX formula
TablaTop = filter(
SUMMARIZE(Historia,Historia[Marca],Historia[Tema],Historia[Año],"Dato",SUM(Historia[Dato])),
Historia[Tema]="PDM"
&&
Historia[Año]=IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año])))
There is, additionaly, a calculated column to distinguish the top 5 from the others
Top = IF(RANKX(TablaTop,TablaTop[Dato],,DESC,Skip)<=5,TablaTop[Marca],"Otros")
Tables related:
Historia is read from excel. Has several years of data.
Años is a one column table that is derived from Historia from Query editor.
let
Source = Historia,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Año"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Año", Order.Ascending}})
in
#"Sorted Rows"
If I put IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año]))) in a measure and put the result in a card visualization it shows the correct result the measure reacts to changes from a slicer with 'Años'[Año] in it. So it works.
The issue:
Table TablaTop does not refreshes at all to slicer selection.
If I replace IF(HASONEVALUE('Años'[Año]),VALUES('Años'[Año]),MAX('Años'[Año]))) in TablaTop definition with a valid year number the results are perfect.
i.e.
TablaTop = filter(
SUMMARIZE(Historia,Historia[Marca],Historia[Tema],Historia[Año],"Dato",SUM(Historia[Dato])),
Historia[Tema]="PDM" &&
Historia[Año]=2016)
This works
Need help to resolve this issue. Any alternative formula is accepted.
Thank you in advance
Solved! Go to Solution.
@Anonymous
hi, please try this
Change your table Top to:
TablaTopVV=
filter(SUMMARIZE(Historia;Historia[Marca];Historia[Tema];Historia[Año];"Dato";SUM(Historia[Dato]));Historia[Tema]="PDM")
Create a calculated Column:
TOP = if(RANKX(FILTER(TablaTopVV,TablaTopVV[Año]=EARLIER(TablaTopVV[Año])),TablaTopVV[Dato])<=5,TablaTopVV[Marca],"OTROS")
A new Measure:
ElDato = CALCULATE(SUM(TablaTopVV[Dato]),FILTER(TablaTopVV,TablaTopVV[Año]=[Año seleccionado]))
Finally in Visual Chart:
Ready!!
Just curious, why not try adding a filter (page or visual level) and setting it for TopN? Then your table will show the top 5 and, provided the relationship is established between Years and the fact table, it should slice it appropriately.
Thank you.
The issue is that it is requiered to include the rest of the data as "Others", so for example, calculating % over Total will include the Top 5 and the rest of the data as well.
Formulas work well when I hard coded the month number, just need to be dynamic. Data is read on a monthly basis and it is not nice to change hard coded formulas every month. Easy to forget!!!.
Hi, I posted a reply with a link to a pbix file to doanload.
Regards
@Anonymous
hi, please try this
Change your table Top to:
TablaTopVV=
filter(SUMMARIZE(Historia;Historia[Marca];Historia[Tema];Historia[Año];"Dato";SUM(Historia[Dato]));Historia[Tema]="PDM")
Create a calculated Column:
TOP = if(RANKX(FILTER(TablaTopVV,TablaTopVV[Año]=EARLIER(TablaTopVV[Año])),TablaTopVV[Dato])<=5,TablaTopVV[Marca],"OTROS")
A new Measure:
ElDato = CALCULATE(SUM(TablaTopVV[Dato]),FILTER(TablaTopVV,TablaTopVV[Año]=[Año seleccionado]))
Finally in Visual Chart:
Ready!!
Great!!!
@VvelardeThank you very much!!!
Could you please explain the logic of the solution.
Regards
Exactly,
I'll will work a little to strip sensitive data and create a sample in order to upload it.
Thank you.
Hi @Anonymous,
Have you resolved your issue? Where is your sample data?
In addition, you can create a measure to show the value in slicer.
selected value=CALCULATE(Max(Table[data]),ALLSELECTED(Table))
Best Regards,
Angelia
Hi,
I have not resolved it yet.
I am attaching a link to download a stripped down page, isolating the issue.
Your help is greatly appreciated.
Regards
https://1drv.ms/u/s!AvtcxjkBouSbqk6Wg_E82PODDz3U
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |