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.
HI Everyone....
I have this table in my PowerBI (am using direct query) the original table is much bigger, this just a sample,
And i need to create a measure to:
How many Customer by District by Date
In target district, each target for each distric
I need to sum how many customer and sum each target to make a gauge.
I got this in PwerBI
Solved! Go to Solution.
Ooops,
I cut and paste the wrong measure in
Please try this
Measure2 = IF( HASONEVALUE( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date],Table1[District], "Max Per Day",MAX('Table1'[Target])),[Max Per Day]) )
Oh yeah, that's not ideal
Please try this slight tweak 🙂
Measure2 = IF( ISFILTERED( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date],Table1[District], "Max Per Day",MAX('Table1'[Target])),[Max Per Day]) )
Hi @sixtoquiles
I created some simple calculated measures using the following forumlas
Count CustID = CALCULATE(DISTINCTCOUNT(Table1[CustomerID])) Target District = CALCULATE(MAX(Table1[Target]))
But equally you might be able to achieve it by dragging the feild to the visual and setting the aggreation to be Count (Distinct)
Thanks.. to take from you time
But I need is a total of TargetDistrict (5+3+4) = 12
This is looking better.
Measure = IF( HASONEVALUE( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date], "Max per day",MAX('Table1'[Target])),[Max per day]) )
HI,
I send you sample data of tabla1.
DATE | District | CustID | Target |
3/26/2017 0:00 | AGENCIA 10 HD | 410791 | 2 |
3/26/2017 0:00 | AGENCIA 10 HD | 704000 | 2 |
3/26/2017 0:00 | AGENCIA 11 HD | 633772 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 633811 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 67401 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 704064 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 704067 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 704070 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 704082 | 4 |
3/26/2017 0:00 | AGENCIA 11 HD | 78323 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 309645 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704054 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704086 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704087 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704089 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704090 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704093 | 4 |
3/26/2017 0:00 | AGENCIA 12 HD | 704094 | 4 |
3/27/2017 0:00 | AGENCIA 11 HD | 550445 | 4 |
3/27/2017 0:00 | AGENCIA 12 HD | 564173 | 4 |
3/28/2017 0:00 | AGENCIA 11 HD | 704192 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 46266 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 480561 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 506622 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 62513 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 704177 | 4 |
3/28/2017 0:00 | AGENCIA 12 HD | 704178 | 4 |
3/29/2017 0:00 | AGENCIA 11 HD | 697391 | 4 |
3/29/2017 0:00 | AGENCIA 11 HD | 704469 | 4 |
The Output in PWBI i need is:
I use sumx but i can't find the correct formula
SUMTARGET = SUMX(SUMMARIZE(Table1,Table1[TARGET]),Table1[TARGET]) (result 6)
SUMTARGET = SUMX(Table1,Table1[TARGET]) (result 112)
Hi @sixtoquiles
I think I see what you mean. This sligtly modifed measure returns 30, (4+4+4+4+4+4+4+2) which I think might be the number you are after????
Measure = IF( HASONEVALUE( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date], "Max Per Day",MAX('Table1'[Target])),[Max Per Day]) )
Ooops,
I cut and paste the wrong measure in
Please try this
Measure2 = IF( HASONEVALUE( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date],Table1[District], "Max Per Day",MAX('Table1'[Target])),[Max Per Day]) )
I HAVE A QUESTION?
If I add a slicer to my PowerBI, to see just one District, look what happens...
Oh yeah, that's not ideal
Please try this slight tweak 🙂
Measure2 = IF( ISFILTERED( 'Table1'[Target] ), MAX(Table1[Target]), SUMX( SUMMARIZE( 'Table1',Table1[Date],Table1[District], "Max Per Day",MAX('Table1'[Target])),[Max Per Day]) )
NOW You HIT!!!
Excellent!!! THANKS!!!
Now and going to study each part to know exactly what's doing the formula, i never imagine use max...
Thanks * 1K =many, many thanks...
THANKS SO MUCH!!!
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |