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.
Im using a formula in my matrix table but I need to keep the decimals in the total column and get rid of them in the other columns.
Is there a way to do that?
Avg Total 2 =
VAR Months =
CALCULATE(COUNTROWS( ALLSELECTED ( PBI[Fecha];PBI[D_Laborales] ));PBI[D_Laborales]<>7)
RETURN
IF (
ISINSCOPE ( PBI[Fecha] );
SUM ( PBI[Total] );
DIVIDE (SUM ( PBI[Total]); Months ))
Is there a way to do that?
Sample DATA:
https://www.dropbox.com/s/pvr3htf4c3m3e0c/BI%20VISITAS%20DEMO%20forum.pbix?dl=0
Solved! Go to Solution.
Hi @Anonymous
After changing your measure as below
Avg Total 2.23 = VAR Months =
CALCULATE(COUNTROWS( ALLSELECTED ( PBI[Fecha],PBI[D_Laborales] )),PBI[D_Laborales]<>7)
RETURN
IF (
ISINSCOPE ( PBI[Fecha] ),
IF(SUM(PBI[Total])<>BLANK(),FORMAT(SUM ( PBI[Total] ),"0")),
DIVIDE (SUM ( PBI[Total]), Months ))
Hi @Anonymous
Create a measure
Avg Total 2.2 =
IF (
HASONEVALUE( PBI[Fecha] ),
IF([Avg Total 2]<>BLANK(),FORMAT([Avg Total 2],"0")),
IF([Avg Total 2]<>BLANK(), FORMAT([Avg Total 2],"0.00"))
)
But it is a measure which is of type Text, you can use the [Avg Total 2] for other calculations.
hI @v-juanli-msft why it gives "0" in the fourth line? it should be 0,14
and it is possible to get a cero insteadoof empty spaces.
Becasue there is something important Id like to see in this matrix, which is to show always everybody from the list even if they have no values in any days.
Please help me with that... it is really hard for me to understand the logic of what you did and find a solution by my self.
Hi @Anonymous
After changing your measure as below
Avg Total 2.23 = VAR Months =
CALCULATE(COUNTROWS( ALLSELECTED ( PBI[Fecha],PBI[D_Laborales] )),PBI[D_Laborales]<>7)
RETURN
IF (
ISINSCOPE ( PBI[Fecha] ),
IF(SUM(PBI[Total])<>BLANK(),FORMAT(SUM ( PBI[Total] ),"0")),
DIVIDE (SUM ( PBI[Total]), Months ))
Try using the Format function:
FORMAT( 12345.67, "General Number")
FORMAT( 12345.67, "Currency")
FORMAT( 12345.67, "Fixed")
FORMAT( 12345.67, "Standard")
FORMAT( 12345.67, "Percent")
FORMAT( 12345.67, "Scientific")
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |