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.
Hello, I need some help to create a report thats shows the sales for the last 4 years and then compare and count all of them to show all clients that buy in 2014, 2015 and 2016, but still don't in 2017.
I created the metrics:
QtdeLiq14 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2014];[Last2014]))
QtdeLiq15 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2015];[Last2015]))
QtdeLiq16 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2016];[Last2016]))
QtdeLiq17 = CALCULATE(SUM('Faturamento 14 - 17'[Qtde liq]);DATESBETWEEN(Dim_Datas[data].[Date];[First2017];[Last2017]))
Lost2017 = IF(([QtdeLiq17]<=0 || ISBLANK([QtdeLiq17])) && [QtdeLiq16]>0 && [QtdeLiq15]>0 && [QtdeLiq14]>0;1)
If I use the metric Lost2017 as a column in the report, I can easily ordenate by that metric. But I don't want to show that column, I want to use that as a filter, to show only the values = 1, and then rank by another column. And, It is not possible to COUNT that metric, I wish to do that too.
I tried to create Lost2017 as a column instead a metric, but the result isn't right, because I have a lot of columns in my table (date, product, region, etc...)
Can someone help me with that?
Solved! Go to Solution.
Hi @LucianaDomene,
If I use the metric Lost2017 as a column in the report, I can easily ordenate by that metric. But I don't want to show that column, I want to use that as a filter, to show only the values = 1, and then rank by another column.
If I understand it correctly, you should be able to use the measure Lost2017 to apply a visual level filter on each visual you want to filter on the report.
And, It is not possible to COUNT that metric, I wish to do that too.
I assume you have a Client dim table called Clients, and it is related to the 'Faturamento 14 - 17' table with the ClentID column. Then you should be able to use the formula below to create a new measure to count all clients that buy in 2014, 2015 and 2016, but still don't in 2017.
measure = SUMX ( 'Clients'; [Lost2017] )
Regards
Hi @LucianaDomene,
If I use the metric Lost2017 as a column in the report, I can easily ordenate by that metric. But I don't want to show that column, I want to use that as a filter, to show only the values = 1, and then rank by another column.
If I understand it correctly, you should be able to use the measure Lost2017 to apply a visual level filter on each visual you want to filter on the report.
And, It is not possible to COUNT that metric, I wish to do that too.
I assume you have a Client dim table called Clients, and it is related to the 'Faturamento 14 - 17' table with the ClentID column. Then you should be able to use the formula below to create a new measure to count all clients that buy in 2014, 2015 and 2016, but still don't in 2017.
measure = SUMX ( 'Clients'; [Lost2017] )
Regards
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 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |