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 all,
I have come upon a recent challenge and thought I'd share here to get some insights.
Consider a standard table as per below :
DATE | TYPE | COUNTRY | UNIT |
01/01/2021 | A | USA | 1 |
01/01/2021 | B | FR | 1 |
01/02/2021 | C | UK | 1 |
Tasked to calculate a ratio, I created a mesure that provides in %, the breakdown between each country. For exemple, results of my measure would be as such :
USA | 33.3% |
FR | 33.3% |
UK | 33.3% |
TOTAL | 100.0% |
Now, besides using a color trick to have the total white and thus invisible, how would you go to disable total calculation for this measure only, knowing that other measures still need to yield a total in the same table:
Country | Metric 1 | Metric 2 |
USA | 33.3% | 5 |
FR | 33.3% | 10 |
UK | 33.3% | 12 |
TOTAL | 27 |
Trying to use IF(HASONEVALUE() to wrap the measure doesn't work, as it needs to be a column. Trying to force a column such as the one were units are stored doesn't work, and still gives me a total.
For those interested, my measure is a Rolling 12 ratio and is quite lenghty:
Take Rate 1Y =
DIVIDE (
CALCULATE (
IF (
COUNTROWS ( VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ) ) = 1,
SUM ( 'MARK ACT GRADE'[SN_CNT] ),
AVERAGEX (
VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ),
SUM ( 'MARK ACT GRADE'[SN_CNT] )
)
),
DATESINPERIOD (
'MARK ACT GRADE'[PRODUCTION DATE],
LASTDATE ( 'MARK ACT GRADE'[PRODUCTION DATE] ),
-12,
MONTH
)
),
CALCULATE (
IF (
COUNTROWS ( VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ) ) = 1,
SUM ( 'MARK ACT GRADE'[SN_CNT] ),
AVERAGEX (
VALUES ( 'MARK ACT GRADE'[PRODUCTION DATE].[MonthNo] ),
SUM ( 'MARK ACT GRADE'[SN_CNT] )
)
),
DATESINPERIOD (
'MARK ACT GRADE'[PRODUCTION DATE],
LASTDATE ( 'MARK ACT GRADE'[PRODUCTION DATE] ),
-12,
MONTH
),
ALLSELECTED ( 'MARK ACT GRADE' )
),
0
)
If somebody has an idea to wrap this in a statement that would allow to hide the total, I am happy to learn.
Thanks,
Stem
Solved! Go to Solution.
Hi @Stemar_Aubert ,
I created the data:
Create measure.
Metric1 =
var _1=COUNTAX(FILTER(ALL('Table'),'Table'[COUNTRY]=MAX('Table'[COUNTRY])),'Table'[COUNTRY])
var _2=COUNTROWS(ALL('Table'))
return
DIVIDE(_1,_2)
Metric2 =
CALCULATE(COUNT('Table'[COUNTRY]),FILTER(ALL('Table'),'Table'[DATE]=MAX('Table'[DATE])))
Form this data:
There are two ways Measure of Total were not displayed:
1. HASONEFILTER:
Create measure.
hasonefilter =
IF(
HASONEFILTER('Table'[DATE]),
IF([Metric2]<>BLANK(),'Table'[Metric2],BLANK()),BLANK())
2. HASONEVALUE
hasonevalue =
IF(HASONEVALUE('Table'[COUNTRY]),[Metric2],BLANK())
3. Result:
If the answer is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Stemar_Aubert ,
Glad to hear that you have sloved problem, you can mark the correct answer, so that others can learn from it too?
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Stemar_Aubert ,
I created the data:
Create measure.
Metric1 =
var _1=COUNTAX(FILTER(ALL('Table'),'Table'[COUNTRY]=MAX('Table'[COUNTRY])),'Table'[COUNTRY])
var _2=COUNTROWS(ALL('Table'))
return
DIVIDE(_1,_2)
Metric2 =
CALCULATE(COUNT('Table'[COUNTRY]),FILTER(ALL('Table'),'Table'[DATE]=MAX('Table'[DATE])))
Form this data:
There are two ways Measure of Total were not displayed:
1. HASONEFILTER:
Create measure.
hasonefilter =
IF(
HASONEFILTER('Table'[DATE]),
IF([Metric2]<>BLANK(),'Table'[Metric2],BLANK()),BLANK())
2. HASONEVALUE
hasonevalue =
IF(HASONEVALUE('Table'[COUNTRY]),[Metric2],BLANK())
3. Result:
If the answer is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Your post has helped me reformulate the question in a better way I believe.
Consider the ContosoSales dataset and below matrix:
Try isfiltered rather than hasonevalue?
Hi,
Doesn't work either. I don't think any function using a column as argument would be effective here unfortunately.
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 |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |