The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi, everyone. I need some help.
I have a database called "Base" with some columns:
1 - ID
2 - Company: Company name
3 - Origin: List of origin cities
4 - Destination: List of destination cities
5 - Amount(kg): Amount of Kg transported
(Example - Base database)
I want to create a table as follows:
(Desired visual - Example)
I have tried the code below, but without success.
Destino Mais Utilizado =
VAR DestinoMaisUtilizado =
TOPN(1,
SUMMARIZE('Base', 'Base'[Destination], "Total KG Carga Paga", SUM('Base'[Amount (kg)])),[Total KG Carga Paga], DESC)
RETURN
SELECTCOLUMNS(DestinoMaisUtilizado, "Destino Mais Utilizado", 'Base'[Destination])
Thanks!
Solved! Go to Solution.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Destination topN table =
VAR _rowcount =
ADDCOLUMNS (
VALUES ( Base[Company] ),
"@rowcount", CALCULATE ( COUNTROWS ( Base ) )
)
VAR _maxrowcount =
MAXX ( _rowcount, [@rowcount] )
VAR _createNtable =
ADDCOLUMNS (
ADDCOLUMNS ( GENERATESERIES ( 1, _maxrowcount, 1 ), "@Top", "Top " ),
"@metricsname", [@Top] & [Value]
)
VAR _addcompanyname =
GENERATE ( Base, _createNtable )
VAR _addresultcolumn =
FILTER (
ADDCOLUMNS (
_addcompanyname,
"@result",
CALCULATE (
SUM ( Base[Amount (kg)] ),
WINDOW (
[Value],
ABS,
[Value],
ABS,
FILTER ( Base, Base[Company] = EARLIER ( Base[Company] ) ),
ORDERBY ( CALCULATE ( SUM ( Base[Amount (kg)] ) ), DESC ),
,
,
MATCHBY ( Base[ID] )
)
)
),
Base[Amount (kg)] = [@result]
)
RETURN
SUMMARIZE (
_addresultcolumn,
Base[ID],
Base[Company],
Base[Destination],
[@metricsname],
[@result]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Destination topN table =
VAR _rowcount =
ADDCOLUMNS (
VALUES ( Base[Company] ),
"@rowcount", CALCULATE ( COUNTROWS ( Base ) )
)
VAR _maxrowcount =
MAXX ( _rowcount, [@rowcount] )
VAR _createNtable =
ADDCOLUMNS (
ADDCOLUMNS ( GENERATESERIES ( 1, _maxrowcount, 1 ), "@Top", "Top " ),
"@metricsname", [@Top] & [Value]
)
VAR _addcompanyname =
GENERATE ( Base, _createNtable )
VAR _addresultcolumn =
FILTER (
ADDCOLUMNS (
_addcompanyname,
"@result",
CALCULATE (
SUM ( Base[Amount (kg)] ),
WINDOW (
[Value],
ABS,
[Value],
ABS,
FILTER ( Base, Base[Company] = EARLIER ( Base[Company] ) ),
ORDERBY ( CALCULATE ( SUM ( Base[Amount (kg)] ) ), DESC ),
,
,
MATCHBY ( Base[ID] )
)
)
),
Base[Amount (kg)] = [@result]
)
RETURN
SUMMARIZE (
_addresultcolumn,
Base[ID],
Base[Company],
Base[Destination],
[@metricsname],
[@result]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
262 | |
120 | |
115 | |
83 | |
70 |