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 friends!
This is my first post here if something is not on the correct form please let me know.
Fist, this is my data
id gerencia cr ValorRevisado ValorRealizado
1 | Gerencia A | CR1 | 15000 | 12578 |
2 | Gerencia B | CR2 | 20000 | 10000 |
3 | Gerencia A | CR2 | 1300 | 1700 |
4 | Gerencia A | CR3 | 500 | 350 |
5 | Gerencia B | CR4 | 900 | 910 |
6 | Gerencia C | CR1 | 800 | 750 |
7 | Gerencia C | CR2 | 50000 | 50000 |
8 | Gerencia C | CR3 | 500 | 1500 |
And this is what im trying to do:
- My rule is :
Measure: Porcentagem = (ValorRealizado / ValorRevisado)
Column (Image URL) = IF Porcentagem > 1: good image else bad image;
I can figure what is happening on table A.
I know what is happening on table B too but my doubt is, is this correct?
I expected somethind like table C, where the rule is applyed after the grouping..
Solved! Go to Solution.
Hi jhonatas,
Based on the measure, if ValorRealizado/ValorRevisado >1, displays good image otherwise it’s bad image. But from the screenshot you provided, it seems you place images in wrong location. For example:
In addition, if you want to add good or bad images after grouping, you need to create a summarize table, then place all fields in a table visual. You can follow steps below:
Create the table.
Add measures.
Calculate percent:
Porcentagem =
var ValorRealizado=SUM([ValorRealizado])
var ValorRevisado=SUM([ValorRevisado])
return
CALCULATE(ValorRealizado/ValorRevisado,ALL(Test))
Format the result to url:
Image = if([Porcentagem]>1, "http://www.clipartbest.com/cliparts/jix/pjG/jixpjGdiE.png","http://www.freeiconspng.com/uploads/checked-correct-right-yes-checkmark-12.png")
Add a calculate column to store and display the url string.
Image Result = [Image]
Create visuals.
Table visual:
Matrix visual:
Use table formula to create a summarize table:
Table = SUMMARIZE(Test,Test[gerencia],"ValorRealizado",SUMX(FILTER(Test,Test[gerencia]=EARLIER(Test[gerencia])), Test[ValorRealizado]),"ValorRevisado",SUMX(FILTER(Test,Test[gerencia]=EARLIER(Test[gerencia])),Test[ValorRevisado]))
Measure and calculate column to display image:
Measure = var ValorRealizado=SUM([ValorRealizado])
var ValorRevisado=SUM([ValorRevisado])
return
CALCULATE(ValorRealizado/ValorRevisado,ALL(Test))
Image = if([Measure]>1,"http://www.clipartbest.com/cliparts/jix/pjG/jixpjGdiE.png","http://www.freeiconspng.com/uploads/checked-correct-right-yes-checkmark-12.png")
Create a table visual:
Regards,
Xiaoxin Sheng
Hello! Me again!
Its possible to do this without creating a summarized table?
Hi @jhonatas,
Yes, it also support.
Create a calculated column with calculate, then use if state to check the result to return the specify image url.
Regards,
Xiaoxin Sheng
I cant make a COLUMN work with all this variables. The context of column in this mess is getting me crazy.
Hi @jhonatas,
Please try to use below formula to create a column on original table:
Image: var temp= calculate(SUM(Table[ValorRealizado])/SUM(Table[ValorRevisado]),Table[Gerencia]=EARLIER(Table[Gerencia]),ALLSELECTED(table)) return if(temp>1, "http://www.clipartbest.com/cliparts/jix/pjG/jixpjGdiE.png","http://www.freeiconspng.com/uploads/checked-correct-right-yes-checkmark-12.png")
Regards,
Xiaoxin Sheng
Hi guys,
When you move the cursor over the icons, the url pops up. Is it possible to no longer occur?
Thank you,
Simona
Hi jhonatas,
Based on the measure, if ValorRealizado/ValorRevisado >1, displays good image otherwise it’s bad image. But from the screenshot you provided, it seems you place images in wrong location. For example:
In addition, if you want to add good or bad images after grouping, you need to create a summarize table, then place all fields in a table visual. You can follow steps below:
Create the table.
Add measures.
Calculate percent:
Porcentagem =
var ValorRealizado=SUM([ValorRealizado])
var ValorRevisado=SUM([ValorRevisado])
return
CALCULATE(ValorRealizado/ValorRevisado,ALL(Test))
Format the result to url:
Image = if([Porcentagem]>1, "http://www.clipartbest.com/cliparts/jix/pjG/jixpjGdiE.png","http://www.freeiconspng.com/uploads/checked-correct-right-yes-checkmark-12.png")
Add a calculate column to store and display the url string.
Image Result = [Image]
Create visuals.
Table visual:
Matrix visual:
Use table formula to create a summarize table:
Table = SUMMARIZE(Test,Test[gerencia],"ValorRealizado",SUMX(FILTER(Test,Test[gerencia]=EARLIER(Test[gerencia])), Test[ValorRealizado]),"ValorRevisado",SUMX(FILTER(Test,Test[gerencia]=EARLIER(Test[gerencia])),Test[ValorRevisado]))
Measure and calculate column to display image:
Measure = var ValorRealizado=SUM([ValorRealizado])
var ValorRevisado=SUM([ValorRevisado])
return
CALCULATE(ValorRealizado/ValorRevisado,ALL(Test))
Image = if([Measure]>1,"http://www.clipartbest.com/cliparts/jix/pjG/jixpjGdiE.png","http://www.freeiconspng.com/uploads/checked-correct-right-yes-checkmark-12.png")
Create a table visual:
Regards,
Xiaoxin Sheng
Ei xioxin, sorry for the late reply. This works perfectly!
Let me ask, i can use this with a date slicer too?
Nevermind, my bad. Its working 100% now, thanks!
maybe i need to write some more details?
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 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |