Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

Reply
unnijoy
Post Prodigy
Post Prodigy

Formula Help

‎03-28-2018 10:59 PM
MonthCountrySiteProgram NameHC
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USGreenvilleRunning464
Jan-18USDalevilleRunning696
Jan-18USDalevilleRunning696
Jan-18USDalevilleRunning696
Feb-18USGreenvilleRunning424
Feb-18USGreenvilleRunning424
Feb-18USGreenvilleRunning424
Feb-18USDalevilleRunning424
Feb-18USDalevilleRunning424
Feb-18USDalevilleRunning424
Feb-18USGreenvilleRunning424

 

Based on the above data i need a formula for ma power bi report in which in the table for for Jan i need the HC for US. As we can see that the program name is repeating. So i need to calcculate the HC in such a way that the formula should calulate the HC once even if the site and program name is repeating. The result should be as follows.

 

MonthcountryHC
Jan-18US1160
Feb-18US824
Labels:
Message 1 of 13
2,419 Views
0
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 04:41 AM

@unnijoy

 

Please try this MEASURE

 

MeasureF =
VAR temp =
    CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) )
RETURN
    SUMX ( temp, [HC] )

Regards
Zubair

Please try my custom visuals

View solution in original post

Message 12 of 13
2,440 Views
0
12 REPLIES 12
Zubair_Muhammad
Community Champion
Community Champion

‎03-28-2018 11:06 PM

HI @unnijoy

 

Try this MEASURE

 

Measure =
SUMX ( VALUES ( Table1[HC] ), CALCULATE ( VALUES ( Table1[HC] ) ) )

Regards
Zubair

Please try my custom visuals
Message 2 of 13
2,350 Views
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-28-2018 11:27 PM

Hi Thanks for the Prompt reply. A small change in the Table.

 

MonthCountrySiteProgram NameModeHC
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USGreenvilleRunningOnline464
Jan-18USGreenvilleRunningILT464
Jan-18USDalevilleRunningOnline696
Jan-18USDalevilleRunningILT696
Jan-18USDalevilleRunningOnline696
Feb-18USGreenvilleRunningILT424
Feb-18USGreenvilleRunningOnline424
Feb-18USGreenvilleRunningILT424
Feb-18USDalevilleRunningOnline400
Feb-18USDalevilleRunningILT400
Feb-18USDalevilleRunningOnline400
Feb-18USGreenvilleRunningILT424

 

In the Table we can see Mode. So the HC should be calculated irrespective of Mode.

By the formula that u gave the system will calculate the HC seperately for each mode. our Formula should not be based on mode.

Message 3 of 13
2,346 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-28-2018 11:45 PM

Hi @unnijoy

 

I get correct results with the revised table even

 

Please see attached file

 

Fhelp.png


Regards
Zubair

Please try my custom visuals
Formula Help.pbix
Message 4 of 13
2,339 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 12:12 AM

Hi @Zubair_Muhammad

 

In that output table we have to include Mode also. We have once for field come in this table. For that we got the formula. Can you please help me to get the data in the below format.

 

MonthcountryModeHC
Jan-18USILT1160
Jan-18USOnline1160
Feb-18USILT824
Feb-18USOnline824
Message 5 of 13
2,335 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 12:36 AM

@unnijoy

 

Even if i include the Mode Field in the Table.. I am getting the required output

 

mode.png


Regards
Zubair

Please try my custom visuals
Formula Help.pbix
Message 6 of 13
2,329 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 12:45 AM

Hi @Zubair_Muhammad

 

I am getting it seperately. Like for December HC filters based on Online and ILT. In the top I have 4 slicers. Month, country, Program name,Mode. Is the mismatch because of this.

Message 7 of 13
2,324 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 12:58 AM

Hi @unnijoy

 

Could you share your file?


Regards
Zubair

Please try my custom visuals
Message 8 of 13
2,318 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 01:14 AM

Hi @Zubair_Muhammad

 

Am not getting a opetion to upload the file.

Message 9 of 13
2,310 Views
0
Zubair_Muhammad
Community Champion
Community Champion
In response to unnijoy

‎03-29-2018 04:41 AM

@unnijoy

 

Please try this MEASURE

 

MeasureF =
VAR temp =
    CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) )
RETURN
    SUMX ( temp, [HC] )

Regards
Zubair

Please try my custom visuals
Message 12 of 13
2,441 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to Zubair_Muhammad

‎03-29-2018 04:56 AM

Hi @Zubair_Muhammad,

 

Amazing. It is working. Thanks al lot for your help and time.

Message 13 of 13
1,965 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to unnijoy

‎03-29-2018 01:51 AM
MonthRegionCountrySiteProgram NameDelivery TypeHC
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaCanadaWHT-ILT205
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSMorrisville-ILT4
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSGreenvilleC45ILT464
Jan-18North AmericaUSWHT-ILT2426
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHT-ILT2053
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Message 11 of 13
1,959 Views
0
unnijoy
Post Prodigy
Post Prodigy
In response to unnijoy

‎03-29-2018 01:25 AM
MonthRegionCountrySiteProgram NameDelivery TypeHC
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaCanadaWHT-ILT205
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSRochester-ILT16
Jan-18North AmericaUSMorrisville-ILT4
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSGreenvilleC45ILT464
Jan-18North AmericaUSWHT-ILT2426
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHTG2ILT2053
Feb-18North AmericaUSWHT-ILT2053
Jan-18North AmericaUSGreenville-ILT464
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSST. LOUISG2ILT20
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSWHT-ILT2426
Jan-18North AmericaUSGreenville-ILT464
Message 10 of 13
2,278 Views
0

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

Check out the April 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All