The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I need to see how many codes in the table on the right are within the range 0-100; 100-500; 500+ per "Month".
To do this I create the following measures
0-100 = IF(SUM(Distancia[Distancia / Kms])<100;1;BLANK())
100-500 = IF(SUM(Distancia[Distancia / Kms])<500 && SUM(Distancia[Distancia / Kms])>100;1;BLANK())
500+ = IF(SUM(Distancia[Distancia / Kms])>500;1;BLANK())
With this I can obtain the table on the left, but I cannot see the sum of the values, for example for "0", the category "0-100" for the month "5" I should see an 8 but the table is not adding the values.
How could you see the sum of the values in the table?
Solved! Go to Solution.
Hi @rodfernandez ,
Based on your description, you can modfiy your 0-100 measure like this:
0-100 =
VAR tab =
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Name],
'Table'[Groupo2],
'Table'[Mes],
"Flag",
IF (
ISBLANK ( SUM ( 'Table'[Distancia / Kms] ) ),
BLANK (),
IF ( SUM ( 'Table'[Distancia / Kms] ) < 100, 1, BLANK () )
)
)
RETURN
SUMX (
FILTER (
tab,
[Flag] = 1
&& [Groupo2] IN DISTINCT ( 'Table'[Groupo2] )
&& [Mes] IN DISTINCT ( 'Table'[Mes] )
&& [Name] IN DISTINCT ( 'Table'[Name] )
),
[Flag]
)
[100-500] measure and [500+] measure are simliar with it.
Result:
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rodfernandez ,
Based on your description, you can modfiy your 0-100 measure like this:
0-100 =
VAR tab =
SUMMARIZE (
ALL ( 'Table' ),
'Table'[Name],
'Table'[Groupo2],
'Table'[Mes],
"Flag",
IF (
ISBLANK ( SUM ( 'Table'[Distancia / Kms] ) ),
BLANK (),
IF ( SUM ( 'Table'[Distancia / Kms] ) < 100, 1, BLANK () )
)
)
RETURN
SUMX (
FILTER (
tab,
[Flag] = 1
&& [Groupo2] IN DISTINCT ( 'Table'[Groupo2] )
&& [Mes] IN DISTINCT ( 'Table'[Mes] )
&& [Name] IN DISTINCT ( 'Table'[Name] )
),
[Flag]
)
[100-500] measure and [500+] measure are simliar with it.
Result:
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@rodfernandez - I'm not sure I follow 100% but seems like you need to use SUMMARIZE and SUMX or something similar. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.